SQL 2008 Converting Row values to columns with dynamic columns

  • Hello to all SQL Experts ,

    I've been working on this query for some time now, I've searched this site and tried some examples but could not get the results that I'm after.

    Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

    If someone kindly help I will be grateful.

    Thank you,

    Sample Data:

    ;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS

    (

    SELECT 5060,'04/30/2015','05:30', '08:30'

    UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'

    UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'

    UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

    )

    ,TEST_TEST AS

    ( SELECT

    SD.ColumnA,

    SD.ColumnB,

    SD.ColumnC,

    SD.ColumnD,

    'SampleTitle'+

    cast(row_number() over(partition by SD.columna, SD.columnb

    order by SD.columnd) as varchar(10)) seq

    from SampleData SD

    )

    select columnA, columnB,columnc

    SampleTitle1, SampleTitle2, SampleTitle3, SampleTitle4

    from

    (

    SELECT

    [ColumnA],

    [ColumnB],

    [ColumnC],

    [ColumnD],

    'SampleTitle'+

    cast(row_number() over(partition by columna, columnb

    order by columnd) as varchar(10)) seq

    from TEST_TEST TT

    ) d

    pivot

    (

    max(columnd)

    for seq in (SampleTitle1, SampleTitle2, SampleTitle3, SampleTitle4)

    ) piv;

    The results from the above are as follows:

    columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4

    506004/30/201505:30 NULL NULL NULL

    506004/30/201513:30 15:30 NULL NULL

    506005/02/201505:30 NULL NULL NULL

    506005/02/201513:30 15:30 NULL NULL

    My desired results with desired headers are as follows:

    PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2

    506004/30/2015 05:30 08:30 13:30 15:30

    506005/02/2015 05:30 08:30 13:30 15:30

  • Quick solution that works with the sample data provided

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    ;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS

    (

    SELECT 5060,'04/30/2015','05:30', '08:30'

    UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'

    UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'

    UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

    )

    ,UNPIVOTED_SET AS

    (

    SELECT

    SD.ColumnA

    ,SD.ColumnB

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SD.ColumnA

    ,SD.ColumnB

    ORDER BY TIME_X.TIME_VAL ASC

    ) AS TIME_RID

    ,TIME_X.TIME_VAL

    FROM SampleData SD

    CROSS APPLY

    (

    SELECT ColumnC AS TIME_VAL UNION ALL

    SELECT ColumnD AS TIME_VAL

    ) AS TIME_X

    )

    SELECT

    US.ColumnA AS PERSON

    ,US.ColumnB AS STARTDATE

    ,MAX(CASE WHEN US.TIME_RID = 1 THEN US.TIME_VAL END) AS STARTIME1

    ,MAX(CASE WHEN US.TIME_RID = 2 THEN US.TIME_VAL END) AS ENDTIME1

    ,MAX(CASE WHEN US.TIME_RID = 3 THEN US.TIME_VAL END) AS STARTTIME2

    ,MAX(CASE WHEN US.TIME_RID = 4 THEN US.TIME_VAL END) AS ENDTIME2

    FROM UNPIVOTED_SET US

    GROUP BY US.ColumnA

    ,US.ColumnB;

    Results

    PERSON STARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2

    ----------- ---------- --------- -------- ---------- --------

    5060 04/30/2015 05:30 08:30 13:30 15:30

    5060 05/02/2015 05:30 08:30 13:30 15:30

  • Hi Eirikur Eiriksson,

    You are the man 🙂

    I really appreciate your help for the second time 🙂

    Can you please guide me to an article or a site where I can further understand/enhance my knowledge of the logic/codes you used to solve my issue?

    Best Regards and have an awesome weekend !!

  • DiabloZA (6/12/2015)


    Hi Eirikur Eiriksson,

    You are the man 🙂

    I really appreciate your help for the second time 🙂

    Can you please guide me to an article or a site where I can further understand/enhance my knowledge of the logic/codes you used to solve my issue?

    Best Regards and have an awesome weekend !!

    It's called a CROSSTAB. The following two articles explain it for both fixed and dynamic applications. When using character based columns, you use MAX like Eirikur did instead of SUM.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply