Pivoting multiple rows into multiple columns in SQL

Question

User230779875 posted

Current table structure :

ID | StartDate | EndDate

1    2/2/2019    3/2/2019

1    4/2/2019    4/2/2019

2     5/2/2019    6/2/2019

output required :

ID StartDate1 EndDate1 StartDate2 Enddate2

1  2/2/2019    3/2/2019  4/2/2019    4/2/2019

2     5/2/2019    6/2/2019

Answer 1

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

Answer 2

User230779875 posted

Is it possible to do without creating a [row] column? or dynamically created row column instead of using temp table?

Answer 3

User-1038772411 posted

Hello Ramyayadav,

Kindly refer the following links for Pivoting multiple rows into multiple columns in SQL using other approaches

1) https://stackoverflow.com/questions/41946039/sql-pivot-rows-to-multiple-columns/41946193#41946193

2) https://stackoverflow.com/questions/41946039/sql-pivot-rows-to-multiple-columns/41946448#41946448

I hope this will help you

Thank you