Click here to Skip to main content
15,885,900 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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
Posted
Updated 25-Jun-21 5:18am
v2
Comments
Meysam Toluie 16-Jul-21 16:14pm    
Update your question in order to make it more clear. For example explain, what your expected result is.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900