SQL Query to transpose table based on year

Question

I have a table in SQL Server that has specific years in a column with values in another column. I would like to make a column for each year and enter its relevant value into the relevant year's column.

I would like to create a view that will show the results but I am not sure how to do this with SQL.

Original table structure:

ID	Year	Value
1	2016	748
2	2018	1613
3	2028	2493
4	2038	2074
5	2048	688
6	2016	818
7	2018	2068
8	2028	1301
9	2038	1726
10	2048	661
11	2016	1413
12	2018	1063
13	2028	1815
14	2038	2546
15	2048	1574
16	2016	861
17	2018	1676
18	2028	1316
19	2038	1498
20	2048	1557

Required structure for view:

ID	Value_2016	Value_2018	Value_2028	Value_2038	Value_2048
1	748	1613	2493	2074	688
2	818	2068	1301	1726	661
3	1413	1063	1815	2546	1574
4	861	1676	1316	1498	1557

Thanks for any help on this.

This is called a [`pivot`](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16). Good answers at [stackoverflow Q&A](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server). — JNevill, Feb 21 '23 at 19:42
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Feb 21 '23 at 19:49

score 3 · Accepted Answer · answered Feb 21 '23 at 19:45

SELECT T.ID,
MAX(
    CASE
      WHEN T.YEAR=2016 THEN T.VALUE
      ELSE 0.00
    END
  )AS VALUE_2016,
MAX(
  CASE
     WHEN T.YEAR=2018 THEN T.VALUE
     ELSE 0.00
  END
 )AS VALUE_2018, 
MAX(
   CASE
     WHEN T.YEAR=2028 THEN T.VALUE
     ELSE 0.00
    END
  )AS VALUE_2028
FROM YOUR_TABLE AS T
GROUP BY T.ID

You can also try "old-style"-pivot as above. If you need dynamic number of columns, please goggle for "dynamic pivot in SQL Server"

SQL Query to transpose table based on year

1 Answers1