locked
Convert rows to columns RRS feed

  • Question

  • I am new to SQL and I have a query that produces the following output for day and night shift.  The data is grouped by employee and shift id. 

    ShiftID Emp Divison ShiftStartDate ShiftStartTime ShiftEndDate ShiftEndTime ORDERNUM SS_Type ShiftSegmentStartTime ShiftSegmentEndTime COMMENTTEXT WORKRULENAME LABORACCTNAME ORGPATHTXT
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 1 Regular 4:45:00 9:45:00 OT - Extended Work NULL ////// NULL
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 2 Break 9:45:00 10:15:00 OT - Extended Work NULL ////// NULL
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 3 Regular 10:15:00 14:45:00 OT - Extended Work NULL ////// NULL
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 4 Break 14:45:00 15:15:00 OT - Extended Work NULL ////// NULL
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 5 Transfer 15:15:00 16:45:00 OT - Extended Work NULL -/-/-/-/-/-/OT - EXTENDED WORK NULL
    633002 789101 ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 1 Transfer 16:45:00 21:45:00 OT - Company Mtg WorkRuleName ////// CompanyName/Division/Department/Unit/Job
    633002 789101 ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 2 Break 21:45:00 22:15:00 OT - Company Mtg NULL ////// NULL
    633002 789101 ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 3 Regular 22:15:00 2:45:00 OT - Company Mtg NULL ////// NULL
    633002 789101 ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 4 Break 2:45:00 3:15:00 OT - Company Mtg NULL ////// NULL
    633002 789101 ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 5 Regular 3:15:00 4:45:00 OT - Company Mtg NULL ////// NULL

     I need the shift segment information converted from rows to columns so that all employee shift details can appear on one row.

    ShiftID Emp Division ShiftStartDate ShiftStartTime ShiftEndDate ShiftEndTime 1 SS_StartTime1 SS_EndTime1 2 SS_StartTime2 SS_EndTime2 3 SS_StartTime3 SS_EndTime3 4 SS_StartTime4 SS_EndTime4 5 SS_StartTime5 SS_EndTime5 COMMENTTEXT WORKRULENAME LABORACCTNAME ORGPATHTXT
    632968 123456 ABC 5/7/2017 4:45:00 5/7/2017 16:45:00 Regular 4:45:00 9:45:00 Break 9:45:00 10:15:00 Regular 10:15:00 14:45:00 Break 14:45:00 15:15:00 Transfer 15:15:00 16:45:00 OT - Extended Work NULL -/-/-/-/-/-/OT - EXTENDED WORK NULL

    Any guidance would be appreciated.

    Thursday, August 24, 2017 6:31 PM

Answers

  • Is there a reasonable maximum value?

    If there not, then it would be better dumping the result into a temporary table and use dynamic sql.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    CREATE TABLE #T (
    ShiftID int,	
    Emp int,
    Divison varchar(15),
    ShiftStartDate date,
    ShiftStartTime time,
    ShiftEndDate date,
    ShiftEndTime time,
    ORDERNUM int,
    SS_Type varchar(15),
    ShiftSegmentStartTime time,
    ShiftSegmentEndTime time,
    COMMENTTEXT varchar(50),
    WORKRULENAME varchar(50),
    LABORACCTNAME varchar(50),
    ORGPATHTXT varchar(50)
    );
    
    INSERT INTO #T (
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime,
    	ORDERNUM,
    	SS_Type,
    	ShiftSegmentStartTime,
    	ShiftSegmentEndTime,
    	COMMENTTEXT,
    	WORKRULENAME,
    	LABORACCTNAME,
    	ORGPATHTXT
    	)
    VALUES
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 1, 'Regular' , '4:45:00' , '9:45:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 2, 'Break	 ' , '9:45:00' , '10:15:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 3, 'Regular' , '10:15:00', '14:45:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 4, 'Break	 ' , '14:45:00', '15:15:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 5, 'Transfer', '15:15:00', '16:45:00	', 'OT - Extended Work	', NULL, '-/-/-/-/-/-/OT - EXTENDED WORK', NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 1, 'Transfer', '16:45:00', '21:45:00	', 'OT - Company Mtg	', 'WorkRuleName', '//////', 'CompanyName/Division/Department/Unit/Job'),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 2, 'Break	  ', '21:45:00', '22:15:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 3, 'Regular' , '22:15:00', '2:45:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 4, 'Break	  ', '2:45:00' , '3:15:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 5, 'Regular' , '3:15:00' , '4:45:00	', 'OT - Company Mtg	', NULL, '//////',	NULL);
    GO
    DECLARE 
    	@columns nvarchar(MAX), 
    	@sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT 
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN SS_Type END) AS ' + QUOTENAME(n) +  
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN ShiftSegmentStartTime END) AS ' + QUOTENAME('ShiftSegmentStartTime' + ' - ' + LTRIM(n)) +
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN ShiftSegmentEndTime END) AS ' + QUOTENAME('ShiftSegmentEndTime' + ' - ' + LTRIM(n)) AS [*]
    FROM dbo.ufn_GetNums(1, (SELECT MAX(ORDERNUM) FROM #T)) AS T
    ORDER BY n
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @SQL = N'
    SELECT
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime,' + @columns + N' 
    FROM
    	#T
    GROUP BY
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime;';
    
    -- SELECT @sql AS sql;
    
    EXEC sys.sp_Executesql @sql;
    GO
    DROP TABLE #T;
    GO

    As you can see, I didn't know what logic apply for these columns (COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT) and excluded them from the pivoting.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, August 24, 2017 8:14 PM
  • How many possible values do you have for ORDERNUM?

    if it is fix then you could use the common way of pivoting:

    - grouping (group by)

    - spreading (case expression)

    - agregating (sum / count / min / max / etc.)

    if there could be any number then you will have to recur to dynamic sql.

    select
        ShiftID, Emp, Divison, ShiftStartDate, ShiftStartTime, ShiftEndDate, ShiftEndTime, 
        max(case when ordernum = 1 then ss_type end) as [1],
        max(case when ordernum = 1 then ShiftSegmentStartTime end) as [ss_starttime_1],
        max(case when ordernum = 1 then ShiftSegmentEndTime end) as [ss_endtime_1],
        ...
        max(case when ordernum = 5 then ss_type end) as [5],
        max(case when ordernum = 5 then ShiftSegmentStartTime end) as [ss_starttime_5],
        max(case when ordernum = 5 then ShiftSegmentEndTime end) as [ss_endtime_5],
        COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT
    from (yourQuery) as T
    group by 
        ShiftID, Emp, Divison, ShiftStartDate, ShiftStartTime, ShiftEndDate, ShiftEndTime, 
        COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT; 


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marked as answer by AGomez3 Friday, August 25, 2017 1:30 PM
    Thursday, August 24, 2017 6:57 PM

All replies

  • Hi,

    Please use the PIVOT Function or CROSS APPLY.

    Thanks,

    Nimish

    for Eg:

    SELECT
        TableName1.Column1,
        TableName1.Column2,
        c.Name Name,
        ISNULL(c.Value,0) Value,
      FROM [TableName1]
      CROSS APPLY
      (
        VALUES
           ('Column1',CAST(Column1 as nvarchar(100))),
          ('Column2',CAST(Column2 as nvarchar(100)))
      ) c (Name,Value)
    
    

    Hope this helps.

    N

     

    Thursday, August 24, 2017 6:43 PM
  • Hello AGomez!

    You can use DISTINC :

    SELECT DISTINCT <columns> from table

    And also you can use GROUP BY:

    SELECT DISTINCT <columns> from table

    GROUP BY <columns>

    If you post your querry it's more easy to help

    SELECT DISTINCT <columns> from table.
    Thursday, August 24, 2017 6:45 PM
  • How many possible values do you have for ORDERNUM?

    if it is fix then you could use the common way of pivoting:

    - grouping (group by)

    - spreading (case expression)

    - agregating (sum / count / min / max / etc.)

    if there could be any number then you will have to recur to dynamic sql.

    select
        ShiftID, Emp, Divison, ShiftStartDate, ShiftStartTime, ShiftEndDate, ShiftEndTime, 
        max(case when ordernum = 1 then ss_type end) as [1],
        max(case when ordernum = 1 then ShiftSegmentStartTime end) as [ss_starttime_1],
        max(case when ordernum = 1 then ShiftSegmentEndTime end) as [ss_endtime_1],
        ...
        max(case when ordernum = 5 then ss_type end) as [5],
        max(case when ordernum = 5 then ShiftSegmentStartTime end) as [ss_starttime_5],
        max(case when ordernum = 5 then ShiftSegmentEndTime end) as [ss_endtime_5],
        COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT
    from (yourQuery) as T
    group by 
        ShiftID, Emp, Divison, ShiftStartDate, ShiftStartTime, ShiftEndDate, ShiftEndTime, 
        COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT; 


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marked as answer by AGomez3 Friday, August 25, 2017 1:30 PM
    Thursday, August 24, 2017 6:57 PM
  • There will be times when ORDERNUM will exceed 5.
    Thursday, August 24, 2017 7:24 PM
  • Thank you!  I just modified my query with your suggestion and it helped a lot.  There is one issue, though.  When one of the SS_Types includes a Transfer, the output appears in two rows.

    ShiftID Emp Division ShiftStartDate ShiftStartTime ShiftEndDate ShiftEndTime 1 ss_starttime_1 ss_endtime_1 2 ss_starttime_2 ss_endtime_2 3 ss_starttime_3 ss_endtime_3 4 ss_starttime_4 ss_endtime_4 5 ss_starttime_5 ss_endtime_5 COMMENTTEXT WORKRULENAME LABORACCTNAME ORGPATHTXT
    633002   12345  ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 NULL NULL NULL Break 21:45:00 22:15:00 Regular 22:15:00 2:45:00 Break 2:45:00 3:15:00 Regular 3:15:00 4:45:00 OT - Company Mtg NULL ////// NULL
    633002 12345  ABC 5/6/2017 16:45:00 5/7/2017 4:45:00 Transfer 16:45:00 21:45:00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL OT - Company Mtg CA WMRF 12hr Ops Night ////// NULL

    Thursday, August 24, 2017 7:48 PM
  • Is there a reasonable maximum value?

    If there not, then it would be better dumping the result into a temporary table and use dynamic sql.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    CREATE TABLE #T (
    ShiftID int,	
    Emp int,
    Divison varchar(15),
    ShiftStartDate date,
    ShiftStartTime time,
    ShiftEndDate date,
    ShiftEndTime time,
    ORDERNUM int,
    SS_Type varchar(15),
    ShiftSegmentStartTime time,
    ShiftSegmentEndTime time,
    COMMENTTEXT varchar(50),
    WORKRULENAME varchar(50),
    LABORACCTNAME varchar(50),
    ORGPATHTXT varchar(50)
    );
    
    INSERT INTO #T (
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime,
    	ORDERNUM,
    	SS_Type,
    	ShiftSegmentStartTime,
    	ShiftSegmentEndTime,
    	COMMENTTEXT,
    	WORKRULENAME,
    	LABORACCTNAME,
    	ORGPATHTXT
    	)
    VALUES
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 1, 'Regular' , '4:45:00' , '9:45:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 2, 'Break	 ' , '9:45:00' , '10:15:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 3, 'Regular' , '10:15:00', '14:45:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 4, 'Break	 ' , '14:45:00', '15:15:00	', 'OT - Extended Work	', NULL, '//////', NULL),
    	(632968, 123456, 'ABC', '5/7/2017', '4:45:00','5/7/2017', '16:45:00', 5, 'Transfer', '15:15:00', '16:45:00	', 'OT - Extended Work	', NULL, '-/-/-/-/-/-/OT - EXTENDED WORK', NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 1, 'Transfer', '16:45:00', '21:45:00	', 'OT - Company Mtg	', 'WorkRuleName', '//////', 'CompanyName/Division/Department/Unit/Job'),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 2, 'Break	  ', '21:45:00', '22:15:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 3, 'Regular' , '22:15:00', '2:45:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 4, 'Break	  ', '2:45:00' , '3:15:00	', 'OT - Company Mtg	', NULL, '//////',	NULL),
    	(633002, 789101, 'ABC', '5/6/2017', '16:45:00','5/7/2017', '4:45:00', 5, 'Regular' , '3:15:00' , '4:45:00	', 'OT - Company Mtg	', NULL, '//////',	NULL);
    GO
    DECLARE 
    	@columns nvarchar(MAX), 
    	@sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT 
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN SS_Type END) AS ' + QUOTENAME(n) +  
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN ShiftSegmentStartTime END) AS ' + QUOTENAME('ShiftSegmentStartTime' + ' - ' + LTRIM(n)) +
    	N', MAX(CASE WHEN ORDERNUM = ' + LTRIM(n) + ' THEN ShiftSegmentEndTime END) AS ' + QUOTENAME('ShiftSegmentEndTime' + ' - ' + LTRIM(n)) AS [*]
    FROM dbo.ufn_GetNums(1, (SELECT MAX(ORDERNUM) FROM #T)) AS T
    ORDER BY n
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @SQL = N'
    SELECT
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime,' + @columns + N' 
    FROM
    	#T
    GROUP BY
    	ShiftID,
    	Emp,
    	Divison,
    	ShiftStartDate,
    	ShiftStartTime,
    	ShiftEndDate,
    	ShiftEndTime;';
    
    -- SELECT @sql AS sql;
    
    EXEC sys.sp_Executesql @sql;
    GO
    DROP TABLE #T;
    GO

    As you can see, I didn't know what logic apply for these columns (COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT) and excluded them from the pivoting.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, August 24, 2017 8:14 PM
  • A reasonable maximum value would be 7 shift segments.  I would be fine with creating the query with the additional two shift segments and leaving those values null.
    Thursday, August 24, 2017 8:27 PM
  • That is because you have multiple values among those four columns I mentioned before (COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT).

    I do not know what logic to use for those.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, August 24, 2017 8:48 PM
  • The values from the row that contains the Transfer will override the COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT values in the other row.
    Thursday, August 24, 2017 9:05 PM
  • What are you using this query for?

    If it's just for presentation then forget about T-SQL and use a tool like SSRS

    Thursday, August 24, 2017 9:46 PM
  • It's not for presentation.  The output will be used to feed another application that requires the data in a single row per shift id per employee.
    Thursday, August 24, 2017 10:08 PM
  • It's not for presentation.  The output will be used to feed another application that requires the data in a single row per shift id per employee.

    Hi AGomez3,

    Welcome to T-SQL forum community.

    Have you got your answer? If yes, please kindly mark the corresponding reply as an answer so that we could know if your issue is solved. If not, Please make a more detailed description about the section which haven't been solved so that we could understand your problem much better.

    By the way, if there is code or data in the content, please copy the script of the code or sample data to the code block when submitting your post so that we could handle your problem more easily.

    Thank you for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 25, 2017 10:45 AM
  • Thanks so much for your help!  Your answers have got me about 90% there. Now I just have to figure out how to combine the values from the row that contains the Transfer since will need to overwrite the COMMENTTEXT, WORKRULENAME, LABORACCTNAME, ORGPATHTXT values in the other row.

    AGomez3

    Friday, August 25, 2017 1:12 PM
  • Hunchback,

    Many thanks!  I'll be trying the dynamic sql solution next. 

    Thanks again,

    AGomez3


    AGomez3

    Friday, August 25, 2017 2:36 PM