Search code examples
sqlsql-serverunpivot

Write a SQL query to convert table from A to B

I have a database table (table A) looks like this, in SQL Server 2016:

Table A:

ID     Group       2018     2019    2020
-----------------------------------------
ID1    Group A      200      300     400
ID2    Group B      100      800     ---
ID2    Group B      ----     500     300

I want to write a SQL query or something like that or a reporting tool to generate a report/table (convert table A to table B) as below:

Table B:

ID       Group   -   Year  -      Value
----------------------------------------
ID1      Group A     2018         200
ID1      Group A     2019         300
ID1      Group A     2020         400
ID2      Group B     2018         100
ID2      Group B     2019         800
ID2      Group B     2019         500
ID2      Group B     2020         300

If it can be achieved by writing a SQL query that would be great. If that it needs to use a programming language to write a program, or use a tool, that will also be OK but please let me know what to use and how to achieve (I know some C# programming).

(I know I should not use ID and Group as the column name. I will not really use that name in the database table, just to simplify the question here)

Anyone can help? Thank you very much!

Solution

  • A canonical method uses union all:

    select id, group, 2018 as year, "2018" from t
union all
select id, group, 2019 as year, "2019" from t
union all
select id, group, 2020 as year, "2018" from t;

    However, in SQL Server, I strongly recommend apply:

    select t.id, t.group, v.*
from t cross apply
     (values (2018, [2018]), (2019, [2019]), (2020, [2020])
     ) v(year, value)
where v.value is not null;