Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Simple Crosstab Procedure with Power

By cgruel,

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.

Total article views: 4307 | Views in the last 30 days: 2
 
Related Articles
FORUM

Pivot

Pivot

FORUM

Pivot Query- need help

Pivot

FORUM

Pivot Table Help Needed

Pivot Table

FORUM

Pivot

Count Distinct in PIVOT

FORUM

color parameter

How to creat a color parameter

Tags
crosstab    
pivot    
sql server 2005    
stored procedure    
t-sql    
 
Contribute