SQL Server Pivot Table Example

What is a Pivot Table?

pivot

A pivot table can aggregate and rotate data from rows to columns. You can also sort, count and aggregate the rows in one table and create a different table while displaying this summarized data. The PIVOT operator, in essence, turns the values of a specified column into column names, effectively rotating a table.

Pivot Table

securities_tbl
Table 1: Securities Table
SELECT *
FROM
(
  SELECT ticker, val, code
  FROM dbo.securities
) AS d
PIVOT
(
  MAX(val)
  FOR code IN (CUSIP, ISIN, BBGTICKER)
) AS piv 

HELPFUL TIP

PIVOT
(Aggregate function (val)
FOR code
IN ( [CUSIP], [ISIN], [BBGTICKER] )
) AS piv

WHERE
• val is the column you want to aggregate
• code is the column you want to pivot
• [CUSIP], [ISIN], and [BBGTICKER] are the headings for the pivoted columns
• piv is the alias for the results of the PIVOT expression

Pivoted Table

As you can see, the resulting pivoted table has been converted so 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: Pivoted 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 *