January 31, 2014 at 3:46 am
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, maybe by using another PIVOT, to produce the same result?
Derek
January 31, 2014 at 8:44 am
Sure, there's a different way to achieve it. I personally prefer the CROSS TABS method for pivoting data.
Here's an example. If you have questions, feel free to ask.
WITH CTE AS(
SELECT ref,
id,
who,
checkin,
ROW_NUMBER() OVER( PARTITION BY ref, id ORDER BY checkin DESC) rn
FROM #t
)
SELECT ref,
MAX( CASE WHEN id = 1 THEN checkin END) AS [1],
MAX( CASE WHEN id = 1 THEN who END) AS who1,
MAX( CASE WHEN id = 2 THEN checkin END) AS [2],
MAX( CASE WHEN id = 2 THEN who END) AS who2,
MAX( CASE WHEN id = 3 THEN checkin END) AS [3],
MAX( CASE WHEN id = 3 THEN who END) AS who3,
MAX( CASE WHEN id = 4 THEN checkin END) AS [4],
MAX( CASE WHEN id = 4 THEN who END) AS who4
FROM CTE
WHERE rn = 1
GROUP BY ref
EDIT: Added column names.
January 31, 2014 at 8:58 am
I just hit reply to post almost the exact same code. 😉
If you want to read about how this works or explore a dynamic version of a similar thing you can read the articles referenced in my signature about cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2014 at 6:23 am
You say 'personally prefer' - how does your solution compare to PIVOTs for performance on large result sets?
Derek
February 3, 2014 at 7:15 am
StarNamer (2/3/2014)
You say 'personally prefer' - how does your solution compare to PIVOTs for performance on large result sets?
It will pretty much always outperform a traditional PIVOT. See this article for a full performance comparison. http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply