SQL - Pivoting / CrossTabs – roommaster

The PIVOTRESULTSET extension is used to convert linear data into a Cross Tab. Cross tabs are still limited to the 32 column maximum of an SQL Result Set.

For example, the following SQL would produce the following result:

SELECT Account,Month,Amount

         FROM CUSTOMERS

         ORDER BY ACCOUNT,MONTH

Account	Month	Amount
101	1	$10.00
101	2	$20.00
101	3	$30.00
102	1	$11.00
102	2	$21.00
102	3	$31.00
103	1	$1.00
103	2	$2.00
103	3	$3.00

For example, the following SQL with the PIVOTRESULTSET WITHTOTALS would produce the following result:

SELECT Account,Month,Amount

         FROM CUSTOMERS

         ORDER BY ACCOUNT,MONTH

         PIVOTRESULTSET WITHTOTALS

Account	1	2	3	Total
101	$10.00	$20.00	$30.00	$60.00
102	$11.00	$21.00	$31.00	$63.00
103	$1.00	$2.00	$3.00	$6.00
Total:	$22.00	$43.00	$64.00	$129.00

To use PIVIOTRESULTSET, there must be at least 3 columns of data and no more than 8 columns of data in your SQL Query.

The last column is the data that will be plotted. (AMOUNT)
The second to last column will be Column Headers. (MONTH)
All unused columns will show on each detail line, up to 5. (ACCOUNT)

In this example, the following SQL with the PIVOTRESULTSET WITHTOTALS would produce the following result:

SELECT Account,AccountName,MONTH,AMOUNT

         FROM CUSTOMERS

         ORDER BY ACCOUNT,MONTH

         PIVOTRESULTSET WITHTOTALS

Account	AccountName	1	2	3	Total
101	Oranges	$10.00	$20.00	$30.00	$60.00
102	Apples	$11.00	$21.00	$31.00	$63.00
103	Bananas	$1.00	$2.00	$3.00	$6.00
Total:		$22.00	$43.00	$64.00	$129.00

Note that any data which is non-existent for a particular cell will show up as Blank, not formatted as 0.00 or 0.

You can use PIVOTRESULTFIELDS to dictate the order of the column headers. This is only needed if not all rows have the same number of columns and the columns should appear in a certain order, such as Months.

In this example, the months January through May will appear at the beginning, regardless of if the first row has data for January through May.

SELECT Account,AccountName,MONTH,AMOUNT

         FROM CUSTOMERS

         ORDER BY ACCOUNT,MONTH

         PIVOTRESULTSET WITHTOTALS

      PIVOTRESULTFIELDS 1,2,3

Account	AccountName	1	2	3	Total
101	Oranges			$30.00	$60.00
102	Apples	$11.00	$21.00	$31.00	$63.00
103	Bananas	$1.00	$2.00	$3.00	$6.00
Total:		$22.00	$43.00	$64.00	$129.00

SELECT Account,AccountName,MONTHNAME(MONTH) ,AMOUNT

         FROM CUSTOMERS

         ORDER BY ACCOUNT,MONTH

         PIVOTRESULTSET WITHTOTALS

      PIVOTRESULTFIELDS January,February,March

Account	AccountName	January	February	March	Total
101	Oranges			30.00	$60.00
102	Apples	$11.00	$21.00	$31.00	$63.00
103	Bananas	$1.00	$2.00	$3.00	$6.00
Total:		$22.00	$43.00	$64.00	$129.00

Related articles