Luis Cazares (7/24/2013)
I believe this option is better for performance.
SELECT ID,SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B
FROM MyTable
GROUP BY ID
ORDER BY ID
For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Thanks, Luis! That link you sent is the best definition of "CrossTabs" and "PivotTables" I've ever seen. I always wondered what those actually were.
And yes... this syntax works very fast... I forgot to set a rowcount, and it scrolled out 42,000 results almost instantly. Cool!
Plus, it is easy to add a "Total" column and sort by that instead.
SELECT ID,
SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B,
SUM( Amount) AS Total
FROM MyTable
GROUP BY ID
ORDER BY Total Desc
Plus, I like how clean and simple it is to add WHERE conditions after MyTable.
Now I just need to do some JOINS to pull in names various things... but thanks to you, I feel I have a great foundation. Thank you so much!