SQL Pivoting using Case Statement
Using Aggregated CASE Statement
I previously illustrated how to using PIVOT table in SQL Server. Today we will cover how to accomplish the same result, using the case statement with aggregate functions.
Securities Table
SELECT ticker, MAX (CASE WHEN code = 'CUSIP' THEN val ELSE NULL END) CUSIP, MAX (CASE WHEN code = 'ISIN' THEN val ELSE NULL END) ISIN, MAX (CASE WHEN code = 'BBGTICKER' THEN val ELSE NULL END) BBGTICKER FROM dbo.securities GROUP BY ticker
HELPFUL TIP
Aggregating using case statement appears to run slightly faster than using the simpler PIVOT function. Also worth noting, that for multi-column pivoting and aggregating you may still need to use case statement. If that weren’t enough, think about this way, SUM/CASE and MAX/CASE has been around for quite some time as is support by most databases and their variants, whereas PIVOT is fairly new and different database support to a lesser degree.
If you as my honest opinion, I would recommend the using CASE method over the PIVOT method.
Converted Table
As you can see, the resulting table has been converted identical in format to PIVOT method. Notice that for each ticker, you can see the CUSIP, ISIN, and BBGTICKER columns… All data is made up, and this bond indicative data is just for illustration purposes only.
Other Related Posts
- SQL Pivoting using Case Statement
This tutorial discusses how to use the case statement , not only does it appear to be faster but there is more vendor support from the major databases. - SQL Server Pivot Table Example
This tutorial discusses the PIVOT operator, which in essence, turns the values of a specified column into column names, effectively rotating a table.
Please Share Us on Social Media
Leave a Reply