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

securities_tbl
Table 1: 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.

pivoted_tbl
Table 2: Converted Table

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

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *