Pivot or Group Table with Multiple Dates as Columns to Rows

Question

With SQL Server, I would like to pivot or group multiple date columns per id like in the example below:

Four columns from table [db].[exampletable], Id, 1/1, 2/1, 3/1

Id	1/1	2/1	3/1
117	10	5	20
118	5	10	15
119	20	15	15
120	20	20	15

Desired Result:

Id	date	sum
117	1/1	10
117	2/1	5
117	3/1	20
118	1/1	5
118	2/1	10
118	3/1	15

this is a straight forward `UNPIVOT` (not PIVOT) – Martin Smith Jul 14 '22 at 19:59 — Martin Smith, Jul 14 '22 at 19:59

score 1 · Answer 1 · answered Jul 14 '22 at 20:00

1

One approach uses a union query:

SELECT Id, '1/1' AS date, [1/1] AS sum FROM yourTable UNION ALL
SELECT id, '2/1', [2/1] FROM yourTable UNION ALL
SELECT id, '3/1', [3/1] FROM yourTable
ORDER BY Id, date;

answered Jul 14 '22 at 20:00

Tim Biegeleisen

502,043
27
286
360

Thanks, but this put all of the sum values in the date columns as well as the sum column – Starbucks Jul 14 '22 at 20:17
@Starbucks This answer should be working for the sample data you provided. Can you be more specific about what problem you see with my answer? – Tim Biegeleisen Jul 14 '22 at 20:26

John Cappelletti · Answer 2 · 2022-07-15T16:38:43.093

Assuming you have variable or a growing number of columns, you can use a bit of JSON to dynamically UNPIVOT your data without having to use Dynamic SQL

Example or dbFiddle

Select A.[ID]
      ,B.* 
 From  YourTable A
 Cross Apply  (
                 Select [date] = try_convert(varchar(25),[key])
                       ,[sum]  = try_convert(int,Value)
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                 Where [key] not in ('Id','OtherColumns','ToExclude')
              ) B

Pivot or Group Table with Multiple Dates as Columns to Rows

2 Answers2