How to turn date values in a column into multiple columns of dates-CodePudding

I need to unpivot this table using T-SQL in SSMS. I don't need to aggregate anything and I am not able to predict the update dates for each ticket. I am using row_number() over partition by to force the sort in the first table and I need to maintain the horizontal sort of dates by ticket in the desired table.

Does anyone have any ideas on how to do this?

Change this table:

rownum	ticket	updated
1	1	1/1/2022
2	1	1/3/2022
1	2	1/27/2022
1	3	3/1/2022
2	3	4/2/2022
3	3	3/1/2022
1	4	7/11/2022

to this table:

ticket	Dt1	Dt2	Dt3
1	1/1/2022	1/3/2022
2	1/27/2022
3	3/1/2022	4/2/2022	5/3/2022
4	7/11/2022

CodePudding user response：

SELECT ticket,
       [dt1],
       [dt2],
       [dt3]
FROM   (SELECT ticket,
               Concat('DT', rownum) AS Col,
               updated
        FROM   TEST) Src
       PIVOT ( Max(updated)
             FOR col IN ( [DT1],
                          [DT2],
                          [DT3] ) ) Pvt

dbfiddle