UpAllNight UpAllNight - 2 months ago 6
SQL Question

How to pivot two rows into one based on date

I have a table that looks like this:

CreatedDate TargetDate Value Name rowNum
2016-10-06 16:30:00.000 2016-10-16 00:00:00.000 17.97566 Joseph 1
2016-10-06 16:20:00.000 2016-10-16 00:00:00.000 1.176129 Heidi 1
2016-10-06 16:20:00.000 2016-10-15 23:00:00.000 1.196976 Heidi 1
2016-10-06 16:30:00.000 2016-10-15 23:00:00.000 15.15687 Joseph 1
2016-10-06 16:30:00.000 2016-10-15 22:00:00.000 11.04526 Joseph 1
2016-10-06 16:20:00.000 2016-10-15 22:00:00.000 1.539218 Heidi 1


And I would like to pivot it into a table like this:

gen_dt_Joseph gen_dt_Heidi TargetDate JosephValue HeidiValue
2016-10-06 16:30:00.000 2016-10-06 16:20:00.000 2016-10-16 00:00:00.000 17.9757 1.1761
2016-10-06 16:30:00.000 2016-10-06 16:20:00.000 2016-10-15 23:00:00.000 15.1569 1.1970
2016-10-06 16:30:00.000 2016-10-06 16:20:00.000 2016-10-15 22:00:00.000 11.0453 1.5392


My query looks like this:

select CreatedDate as gen_dt_Joseph
,CreatedDate as gen_dt_Heidi
,TargetDate [TargetDate]
,[Joseph] [JosephValue]
,[Heidi] [HeidiValue]
FROM theTable
pivot (max(value) for Name in ( [Joseph],[Heidi])
) as pvt
order by TargetDate DESC


Of course this is returning:

gen_dt_Joseph gen_dt_Heidi TargetDate JosephValue HeidiValue
2016-10-06 16:30:00.000 2016-10-06 16:30:00.000 2016-10-16 00:00:00.000 17.9757 NULL
2016-10-06 16:20:00.000 2016-10-06 16:20:00.000 2016-10-16 00:00:00.000 NULL 1.1761
2016-10-06 16:30:00.000 2016-10-06 16:30:00.000 2016-10-15 23:00:00.000 15.1569 NULL
2016-10-06 16:20:00.000 2016-10-06 16:20:00.000 2016-10-15 23:00:00.000 NULL 1.1970
2016-10-06 16:30:00.000 2016-10-06 16:30:00.000 2016-10-15 22:00:00.000 11.0453 NULL
2016-10-06 16:20:00.000 2016-10-06 16:20:00.000 2016-10-15 22:00:00.000 NULL 1.5392


So basically I want one record containing two values for each TargetDate, but if I have two different CreatedDate's I want to record the most recent one for each record. Can anyone help me take the final step here?

Answer

When having to pivot multiple columns I prefer to simply use the CASE WHEN statement combined with a standard GROUP BY clause. The following query will give you the desired result .

SELECT      MAX(
                CASE Name 
                    WHEN    'Joseph' THEN CreatedDate
                    ELSE    NULL
                END
            ) AS gen_dt_Joseph
            ,MAX(
                CASE Name 
                    WHEN    'Heidi' THEN CreatedDate
                    ELSE    NULL
                END
            ) AS gen_dt_Heidi
            ,TargetDate
            ,MAX(
                CASE Name 
                    WHEN    'Joseph' THEN Value
                    ELSE    NULL
                END
            ) AS JosephValue
            ,MAX(
                CASE Name 
                    WHEN    'Heidi' THEN Value
                    ELSE    NULL
                END
            ) AS HeidiValue
FROM        theTable
GROUP BY    TargetDate
ORDER BY    TargetDate DESC