asked
How do I match values from a PIVOT?
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs. Here's some test data and code which shows the principle: CREATE TABLE #t ( ref INT , id INT , who VARCHAR(10) , checkin DATE ) INSERT #t ( ref, id, who, checkin ) VALUES ( 123, 1, 'andy', '2014-1-16' ), ( 123, 1, 'bill', '2014-1-17' ), ( 123, 1, 'carol', '2014-1-18' ), ( 123, 2, 'diana', '2014-1-16' ), ( 123, 2, 'andy', '2014-1-18' ), ( 123, 3, 'bill', '2014-1-16' ), ( 123, 4, 'carol', '2014-1-17' ), ( 123, 4, 'diana', '2014-1-16' ), ( 456, 2, 'diana', '2014-1-17' ) SELECT res.ref , [1] , who1 , [2] , who2 , [3] , who3 , [4] , who4 FROM ( SELECT ref , [1] , [2] , [3] , [4] FROM ( SELECT ref , id , checkin FROM #t ) src PIVOT ( MAX(checkin) FOR id IN ( [1], [2], [3], [4] ) ) pvt ) res LEFT JOIN ( SELECT ref , who AS who1 , MAX(checkin) AS checkin FROM #t WHERE id = 1 GROUP BY ref , who ) one ON res.[1] = one.checkin AND res.ref = one.ref LEFT JOIN ( SELECT ref , who AS who2 , MAX(checkin) AS checkin FROM #t WHERE id = 2 GROUP BY ref , who ) two ON res.[2] = two.checkin AND res.ref = one.ref LEFT JOIN ( SELECT ref , who AS who3 , MAX(checkin) AS checkin FROM #t WHERE id = 3 GROUP BY ref , who ) three ON res.[3] = three.checkin AND res.ref = one.ref LEFT JOIN ( SELECT ref , who AS who4 , MAX(checkin) AS checkin FROM #t WHERE id = 4 GROUP BY ref , who ) four ON res.[4] = four.checkin AND res.ref = one.ref DROP TABLE #t The result set is: ref 1 who1 2 who2 3 who3 4 who4 123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol 456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL Is there some way to avoid all the LEFT JOINs, may be by using another PIVOT, to produce the same result? PS: How do I insert a table here? I tried HTML and it got stripped out!
No need for the left joins. Get the data of the max events in the right structure and then pivot that... ;with cte as ( SELECT ref , id , who, checkin, row_number()over(partition by ref, id order by checkin desc) as rn FROM #t ) ,maxevents as ( select ref , 'who'+cast(id as varchar) as ColHeader, who as ColContent from cte where rn=1 union all select ref , cast(id as varchar(10)), cast(checkin as varchar(10)) from cte where rn=1 ) select ref , [1], who1, [2], who2, [3], who3, [4], who4 FROM maxevents PIVOT ( MAX(ColContent) FOR ColHeader IN ( [1], who1, [2], who2, [3], who3, [4], who4 ) ) pvt
After discussion with one of my colleagues, we came up with the same answer. Of course, we will need to get the conversions right for datetime columns in particular.
13 People are following this question.
