Help with a SQL Query to combine records in a single row

  • Hello Everyone,

    I'm working on a report where my table is as follows:

    WITH SampleData (ID,NAME,[VALUE]) AS

    (

    SELECT 170983,'DateToday','6/04/2014'

    UNION ALL SELECT 170983,'DateToday','6/04/2014'

    UNION ALL SELECT 170983,'employee','1010'

    UNION ALL SELECT 170983,'employee','1010'

    UNION ALL SELECT 170983,'HoursPerDay','8:00'

    UNION ALL SELECT 170983,'HoursPerDay','8:00'

    UNION ALL SELECT 170983,'LeaveEndDate','6/16/2014'

    UNION ALL SELECT 170983,'LeaveEndDate','6/16/2014'

    UNION ALL SELECT 170983,'LeaveStartDate','6/16/2014'

    UNION ALL SELECT 170983,'LeaveStartDate','6/16/2014'

    UNION ALL SELECT 170983,'LeaveType','Personal'

    UNION ALL SELECT 170983,'LeaveType','Personal'

    UNION ALL SELECT 171024,'DateToday','6/04/2014'

    UNION ALL SELECT 171024,'DateToday','6/04/2014'

    UNION ALL SELECT 171024,'employee','1'

    UNION ALL SELECT 171024,'employee','1'

    UNION ALL SELECT 171024,'HoursPerDay','8:00'

    UNION ALL SELECT 171024,'HoursPerDay','8:00'

    UNION ALL SELECT 171024,'LeaveEndDate','6/17/2014'

    UNION ALL SELECT 171024,'LeaveEndDate','6/17/2014'

    UNION ALL SELECT 171024,'LeaveStartDate','6/17/2014'

    UNION ALL SELECT 171024,'LeaveStartDate','6/17/2014'

    UNION ALL SELECT 171024,'LeaveType','Bereavement1'

    UNION ALL SELECT 171024,'LeaveType','Bereavement1'

    )

    SELECT

    *

    FROM SampleData;

    Here is my query against the table above:

    SELECT

    ID

    ,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON

    ,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE

    ,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE

    ,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE

    ,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS

    ,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE

    FROM SampleData

    Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):

    IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE

    170983NULL6/04/2014NULL NULL NULL NULL

    1709831010NULL NULL NULL NULL NULL

    170983NULLNULL NULL NULL 8:00 NULL

    170983NULLNULL NULL 6/16/2014 NULL NULL

    170983NULLNULL 6/16/2014 NULL NULL NULL

    170983NULLNULL NULL NULL NULL Personal

    171024NULL6/04/201 NULL NULL NULL NULL

    1710241NULL NULL NULL NULL NULL

    171024NULLNULL NULL NULL 8:00 NULL

    171024NULLNULL NULL 6/17/2014 NULL NULL

    171024NULLNULL 6/17/2014 NULL NULL NULL

    171024NULLNULL NULL NULL NULL Bereavement

    My Desired results are as follows:

    IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE

    17098310106/04/20146/16/2014 6/16/2014 8:00 Personal

    17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement

    Thank you for all the help..

  • When I execute your original statement, I get an error saying ID cannot be in the SELECT list because of the aggregation functions and there is no gruop by.

    So I added the GROUP BY clause:

    SELECT

    ID

    ,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON

    ,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE

    ,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE

    ,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE

    ,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS

    ,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE

    FROM SampleData

    GROUP BY ID

    And this query gives the desired result set.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi Koen Verbeeck,

    So that's all I needed :), so I guess I had the rest setup okay.

    Good deal thanks.

    Take care.

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

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