UberCrossTab was created out of a need to create crosstab data as easily as MS Access does. It leverages SQL Server 2005's PIVOT function so it will NOT work in SQL Server 2000.
- The only variables that are needed are the first 5.
- It gets really interesting when you start using the others.
- The "col" variables can reference another table allowing for a fully dynamic list of columns even if the data isnt in the pivot table.
- Store the pivoted data into a table if desired.
- Also you can use a select string in quotes as the pivot table, allowing for all kinds of joins.
So, if you had a table setup like...
Table1
| ID |
Type |
Color |
| 1 |
A |
RED |
| 2 |
A |
RED |
| 3 |
B |
RED |
| 4 |
C |
BLUE |
| 5 |
D |
GREEN |
To get a crosstab showing Type as the rows and Color as the columns counting on ID you would use...
Note: The YOURDB reference is not needed if you stored the proc in the same database as your table.
EXECUTE [YOURDB].[dbo].[UberCrosstab]
@pivotRowFields = 'Type'
,@pivotField = 'Color'
,@pivotTable = '[YOURDB].[dbo].[Table1]'
,@aggField = 'ID'
,@aggFunc = 'COUNT'
Which returns...
| TYPE |
RED |
BLUE |
GREEN |
| A |
2 |
0 |
0 |
| B |
1 |
0 |
0 |
| C |
0 |
1 |
0 |
| D |
0 |
0 |
1 |
I hope this can be of use to some.
