0 votes
in Education by (1.7m points)
I have this table where columns correspond to names:

i.e. 11 = first, 12 = second, 13 = third..

COLUMNID(string)  starttime(string) endtime(string)

11                 20111203123132    20101203143239

11                 20101203143156     20101203143322

11                     ...               ...

12

12

13

14

16

17

18

18

I would need a query to give me names instead of ID

COLUMNAME(string)  starttime(string) endtime(string)

FIRST                 20111203123132    20101203143239

FIRST                 20101203143156     20101203143322

FIRST                    ...               ...

FIRST

FIRST

SECOND

SECOND

THIRD

...

SOLUTION:

SELECT ID, USERID, ( CASE WHEN SUBSTR(USERID,1,4) LIKE 'CC0%' THEN 'ADMIN' WHEN SUBSTR(USERID,1,4) LIKE 'CC1%' THEN 'THEO' WHEN SUBSTR(USERID,1,4) LIKE 'CC12%' THEN 'PAT' WHEN SUBSTR(USERID,1,4) LIKE 'CC13%' THEN 'DOUG' WHEN SUBSTR(USERID,1,4) LIKE 'C22%' THEN 'PHIL' WHEN SUBSTR(USERID,1,4) LIKE 'K15%' THEN 'SONIA' WHEN SUBSTR(USERID,1,4) LIKE 'k16%' THEN 'JEAN' WHEN SUBSTR(USERID,1,4) LIKE 'P58%' THEN 'FAB' WHEN SUBSTR(USERID,1,4) LIKE 'P9%' THEN 'LOG' ELSE 'N/A' END ) USERNAME FROM LOG_HISTORY

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
This depends on what database type you are targetting. If you are targeting SQL Server, I would suggest exploring something like this

SELECT Name =

   CASE COLUMNID

      WHEN '11' THEN First

      WHEN '12' THEN Second

   END,

   starttime, endtime

FROM yourtable

You may want to look at your database design. I'm not sure what you curently have is the best to achieve the result you seem to want. Is there a reason starttime and endtime columns are strings instead of datetime?

Edit : Modified Query to match question update
...