Hello folks,
I have a following data with 2 Unique Identifiers (ID1, ID2), and multiple values that spread across various times. I am trying to pivot it dynamically, because one combination of ID1 & ID2 do not have a fixed number of Values and Times (could be anywhere from 1-100) I was able to figure it out with a single column, but can't figure out how to do both Time and Val in sequence side-by-side.
Any help is greatly appreciated! Thanks!
DECLARE @DataSource TABLE
(
ID1 INT
,ID2 INT
,Val INT
,[Time] Datetime
)
INSERT INTO @DataSource (ID1, ID2, Val, [Time])
VALUES (1,1,10,'01/01/2021 12:00')
,(1,1,20,'01/01/2021 15:00')
,(1,2,30,'01/02/2021 17:00')
,(1,2,35,'01/02/2021 18:00')
,(2,1,40,'02/02/2021 08:00')
,(2,2,50,'02/02/2021 10:00')
,(2,2,60,'05/01/2021 11:00')
SELECT *
FROM @DataSource<pre lang="SQL">
What I have tried:
DECLARE @cols1 nvarchar(max)
DECLARE @cols2 nvarchar(max)
DECLARE @sql1 nvarchar(max)
SELECT @cols1 = STUFF((SELECT ',' + QUOTENAME(t.TheVal) FROM (SELECT DISTINCT 'Val' + CAST(ROW_NUMBER() OVER (PARTITION BY ID1, ID2 order by Time desc) AS VARCHAR(100)) as TheVal FROM @DataSource) as t FOR XML PATH (''), TYPE).value ('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT @cols2 = STUFF((SELECT ',' + QUOTENAME(t.TheTime) FROM (SELECT DISTINCT 'Time' + CAST(ROW_NUMBER() OVER (PARTITION BY ID1, ID2 order by Time desc) AS VARCHAR(100)) as TheTime FROM @DataSource) as t FOR XML PATH (''), TYPE).value ('.', 'NVARCHAR(MAX)'), 1, 1, '')
set @sql1 = 'SELECT ID1, ID2, ' + @cols1 + ' from
(
select ID1, ID2, TheVal, Val FROM @DataSource
) as x1
pivot (max(Val) for TheVal IN (' + @cols1 + ')
) as p1
'
EXEC sp_executesql @sql1