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...
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.
@pivotRowFields = 'Type'
,@pivotField = 'Color'
,@pivotTable = '[YOURDB].[dbo].[Table1]'
,@aggField = 'ID'
,@aggFunc = 'COUNT'
I hope this can be of use to some.