Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 4218 | Views in the last 30 days: 30
 
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    
procedure    
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones