SQL - Pivoting / CrossTabs
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 |