0

I'm using SQL Server 2017. I have a table like the following:

+------+----------+-----+
| DATE | CATEGORY | SUM |
+------+----------+-----+
| 1/20 | A        |   4 |
| 1/20 | B        |   7 |
| 1/20 | C        |   3 |
| 1/21 | A        |   6 |
| 1/21 | B        |   2 |
| 1/21 | C        |   9 |
+------+----------+-----+

I want to transform it into this, with a column for each category and single row for each date:

+------+----------+----------+----------+
| DATE | Sum of A | Sum of B | Sum of C |
+------+----------+----------+----------+
| 1/20 |        4 |        7 |        3 |
| 1/21 |        6 |        2 |        9 |
+------+----------+----------+----------+

The catch is the number of columns will vary between runs of the query. Is there a way to write SQL to do generate a column for each category without having to define each category individually in the select statement? The number of categories will vary between 30-50, I'd like to be able to automatically return only columns corresponding to a category. Is this possible? I'm not sure where to start. Thanks so much!

Shawn Strasser
  • 11
  • 2
  • 1
    If the number of categories is variable you'll need to use dynamic SQL. The duplicate shows how to do it. – Nick Feb 04 '20 at 03:43
  • USE Pivot IN sql – JonWay Feb 04 '20 at 08:17
  • Your answer CREATE TABLE #TBL ([DATE] VARCHAR(10), CATEGORY VARCHAR(10), [SUM] INT) INSERT INTO #TBL VALUES ('1/20','A',4),('1/20','B',7),('1/20','C',3),('1/21','A',6),('1/21','B',2),('1/21','C',9) SELECT * FROM #TBL PIVOT ( SUM([SUM]) FOR CATEGORY IN ([A],[B],[C]) ) PV DROP TABLE #TBL – JonWay Feb 04 '20 at 08:24
  • Thanks @Nick you're right that is exactly what I'm trying to do! Sorry for posting a duplicate I didn't see that post before. I wasn't sure the right search terminology. Thanks so much! – Shawn Strasser Feb 04 '20 at 09:34
  • Thanks @JonWay that does work for the sample data though though the dynamic SQL turns out to also be a key I was missing. Thanks for your response! – Shawn Strasser Feb 04 '20 at 09:35

0 Answers0