User-1038772411 posted
Hii Ramyayadav,
Try with this.
CREATE TABLE #Test (
ID INT,
[Row] INT,
[StartDate ] DATETIME,
[EndDate] DATETIME
)
INSERT #Test
VALUES
(1, 1, '2/2/2019', '3/2/2019'),
(1, 2, '4/2/2019', '4/2/2019'),
(2, 1, '5/2/2019', '6/2/2019')
To get your expected result you have to use UNPIVOT
before using PIVOT
like below:
SELECT *
FROM (
SELECT ID, ITEM + CAST([Row] AS VARCHAR) AS Name, VALUE
FROM (
SELECT *
FROM #Test
)s
UNPIVOT
(VALUE FOR Item IN ([StartDate], [EndDate])) p
) src
PIVOT
(MAX(VALUE) FOR Name IN ([StartDate1], [EndDate1], [StartDate2], [EndDate2])
) pvt
Obviously, the line (MAX(VALUE)
FOR Name IN ([StartDate1],
[EndDate1],
[StartDate2], [EndDate2])
depends on count of different values in the field [Row] so the query has to
be created dynamically.
Instead of using UNPIVOT/PIVOT
you could create dynamically a query which will use OUTER APPLY.
SELECT DISTINCT ID,
t1.[StartDate] AS StartDate1,
t1.[EndDate] AS EndDate1,
t2.[StartDate] AS StartDate2,
t2.[EndDate] AS EndDate2
FROM #Test t
OUTER APPLY (
SELECT TOP 1 [StartDate], [EndDate]
FROM #Test
WHERE ID = t.ID
AND [Row] = 1
) t1
OUTER APPLY (
SELECT TOP 1 [StartDate], [EndDate]
FROM #Test
WHERE ID = t.ID
AND [Row] = 2
) t2
Reference : https://stackoverflow.com/questions/48638218/how-to-convert-multiple-row-data-to-multiple-column-using-pivot-in-sql-server
Thank You