0
TABLE1
STUDENT TIME    SCORE   CLASS
1   1   2   A
1   2   6   A
1   3   7   B
1   4   0   C
2   1   8   C
2   2   1   C
2   4   2   B
3   2   9   A
4   2   2   A
4   3   5   A

I have the TABLE1 as shown above and wish to create this

STUDENT SCORE1A SCORE2  SCORE3B SCORE4
1   2   6   7   0
2   8   1   NA  2
3   NA  9   NA  NA
4   NA  2   5   NA

My trial to do so, fails as such:

    SELECT STUDENT,
    CASE WHEN TIME = 1 AND CLASS = 'A', SCORE END AS SCORE1A,
    CASE WHEN TIME = 2, SCORE END AS SCORE2,
CASE WHEN TIME = 3 AND CLASS = 'B', SCORE END AS SCORE3B
        CASE WHEN TIME = 4, SCORE END AS SCORE4
FROM TABLE1
bvowe
  • 3,004
  • 3
  • 16
  • 33
  • 1
    You have the incorrect syntax for your `case` statements. The keyword is `THEN` not `,`. e.g. `CASE WHEN TIME = 1 THEN SCORE END AS SCORE1 – EdmCoff Feb 06 '23 at 23:16
  • @EdmCoff I tried that modification but still get error results. – bvowe Feb 07 '23 at 00:13
  • 1
    What is the actual error message? What is your full query (you don't have have your `from` clause listed)? – EdmCoff Feb 07 '23 at 00:53
  • @EdmCoff It no executes without error but it does not product the desired output. It produces the correct VALUES but also includes a bunch of NULL values for the ones I do not want...please can you advise? – bvowe Feb 07 '23 at 15:36
  • If your problem now is that you are seeing nulls instead of "NA"s, you need to put the "NA"s in your case statement as part of the `else`. e.g. `CASE WHEN TIME = 1 THEN SCORE ELSE 'NA' END AS SCORE1`. That should work as-is in MySQL, but in other DBMSes you would also need to convert `score` to be a character type. – EdmCoff Feb 07 '23 at 16:20
  • @EdmCoff but there shouldn't be any missing...I'm wondering if you can assist in the query? – bvowe Feb 07 '23 at 19:37
  • Oh, I see, you don't just want one row returned for each row in the table. You want a pivot. Please see the answer linked when your ticket was closed. If you really want you can put _subqueries_ in your `SELECT`, but the pivot will be more maintainable. – EdmCoff Feb 07 '23 at 21:16

0 Answers0