Restructure the SQL query based on some column value

Question

Suppose there's a table like this:

    +-------+-------+-------+
    |YEAR   |Name   |Score  |
    +-------+-------+-------+
    |2019   |Bob    |80     |
    |2019   |David  |90     |
    |2019   |Green  |92     |
    |2019   |Tom    |85     |
    |2020   |Bob    |79     |
    |2020   |David  |87     |
    |2020   |Green  |98     |
    |2020   |Tom    |90     |
    +-------+-------+-------+

I want to write a sql query, and the desired output will look like this:

    +-------+-------+-------+-------+-------+
    |Date   |Bob    |David  |Green  |Tom    |
    +-------+-------+-------+-------+-------+
    |2019   |80     |90     |92     |85     |
    |2020   |79     |87     |98     |90     |
    +-------+-------+-------+-------+-------+

Any help ?

Does this answer your question? [SQL Transpose Rows as Columns](https://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns) — snipsnipsnip, Jul 17 '20 at 12:18
If you don't know the specific list of names, you will need dynamic SQL. Your question requires a database tag. But if you google your database with "dynamic pivot" you can probably find example code. — Gordon Linoff, Jul 17 '20 at 12:18
What's the difference between `pivot` and `transpose` ? (ps: I am applying sql query on a mysql table) — g4ls0n, Jul 17 '20 at 12:52

Alihan ÖZ · Answer 1 · 2020-07-20T13:45:44.617

1

You may use PIVOT clause.

SELECT * FROM table_name
PIVOT(
    MAX(SCORE) 
    FOR YEAR
    IN ( 
        2019,
        2020
    )
)
ORDER BY NAME;

You can check this link for more detail.

edited Jul 20 '20 at 13:45

answered Jul 17 '20 at 12:45

Alihan ÖZ

173
1
7

I think you meant MAX(SCORE) instead of COUNT(SCORE). – Ankit Bajpai Jul 17 '20 at 15:23
My bad, I fixed it. – Alihan ÖZ Jul 20 '20 at 13:46

score 0 · Accepted Answer · answered Jul 17 '20 at 15:03

I have changed Name as nm, Year as yr. Try this:

Select distinct yr Date,
MAX(CASE WHEN nm = 'Bob' THEN score  END) OVER (PARTITION BY yr)Bob,
MAX(CASE WHEN nm = 'David' THEN score  END) OVER (PARTITION BY yr) David,
MAX(CASE WHEN nm = 'Green' THEN score  END) OVER (PARTITION BY yr)Green,
MAX(CASE WHEN nm = 'Tom' THEN score  END) OVER (PARTITION BY yr)Tom
from table
where yr in ('2019', '2020');

Output:

+------+-----+-------+-------+-----+
| date | Bob | David | Green | Tom |
+------+-----+-------+-------+-----+
| 2019 |  80 |    90 |    92 |  85 |
+------+-----+-------+-------+-----+
| 2020 |  79 |    87 |    98 |  90 |
+------+-----+-------+-------+-----+

Restructure the SQL query based on some column value

2 Answers2