locked
Pivoting multiple rows into multiple columns in SQL RRS feed

  • 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

    Friday, June 7, 2019 10:05 AM

All replies

  • 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

    Friday, June 7, 2019 10:25 AM
  • User230779875 posted

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

    Sunday, June 9, 2019 5:26 PM
  • 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

    Monday, June 10, 2019 5:03 AM