Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 15 posts - 106 through 120 (of 130 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 1

Dennis Wagner-347763 (4/30/2010)
Super article Jeff -- as always. Thanks especially for writing something that works in SQL 2000 (until you hit the 4000 char limit). A number of us out there are still supporting some SQL 2000 systems.
Here is one for your "Super Ninja" -- use the GROUPING function to help with those gnarly ROLLUPS and CUBES.
Select part:
SELECT CASE GROUPING(Field1) WHEN 1 THEN 'Total' ELSE Field1 END -- puts "Total" in when rolled up
Order by part:
ORDER BY GROUPING(Field1) ASC, Field1 (sorts the totals to the bottom, change ASC to DESC to sort them to the top)
And finally when using WITH CUBE because you want some of the other dimensions but not all of them, use the HAVING clause to eliminate some of them
HAVING GROUPING(Field3) = 0 -- will not roll up this field into a total
AND GROUPING(Field4) = GROUPING(Field5) -- rolls these up together, which is perfect when Field4 is the ID and Field5 is the description and you don't want to roll them up separately
This works with WITH ROLLUP too!

I agree... WITH ROLLUP and CUBE add little time to the overall duration and GROUPING makes it understandable. Heh... without it, most folks just can't get their arms around what all the NULLs mean especially when using WITH CUBE.

Thanks for the feedback, Dennis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice article Jeff. Nice to see it republished so we can review it again.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

peter-757102 SSCertifiable Points: 6877 More actions · Answer 3

peter-757102

SSCertifiable

Points: 6877

May 3, 2010 at 3:04 am

#1161146

Good one Jeff.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

Thanks for the feedback Jason and Peter.

Rumor has it that Steve is working on making a couple of training "movies" for the two articles in this series. He's pretty darned good at such things. I can't wait to see what he comes up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xxxxxxxxxxxxx SSC Journeyman Points: 83 More actions · Answer 5

Another excellent article, Jeff. I liked it as much as, or better than, part one. I especially liked your usage of concatenation to generate the columns in the SELECT clause. Does this trick work in other DBMS's, do you know? Thanks so much for the valuable contribution!

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 6

Doug Bass (5/4/2010)
Another excellent article, Jeff. I liked it as much as, or better than, part one. I especially liked your usage of concatenation to generate the columns in the SELECT clause. Does this trick work in other DBMS's, do you know? Thanks so much for the valuable contribution!

Thanks for the feedback Doug,

Overlaying variables in a single SELECT in fact DOESN'T work in a lot of other RDBMSs. It was always one of the frustrations I suffered when I had to do work with Oracle. It does, however, work with SyBase which is also based on the "Rushmore" RDBMS engine and is where SQL Server got its roots from.

Do be advised that that type of concatenation can have it's problems. I covered some of the problems in the following article along with some of the remediations...

http://www.sqlservercentral.com/articles/Test+Data/61572/

There's also a speedier way to do the concatenation using FOR XML PATH('') starting in SQL Server 2005. That method is briefly covered at the end of that same concatenation article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ziangij SSCrazy Eights Points: 8066 More actions · Answer 7

ziangij

SSCrazy Eights

Points: 8066

May 7, 2010 at 3:54 am

#1163910

great article... thank you 🙂

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 8

ziangij (5/7/2010)
great article... thank you 🙂

You're welcome. Thanks for both the visit and the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pac123 SSCrazy Points: 2464 More actions · Answer 9

Hi Jeff,

Fantastic article, i have to create a Dynamic cross tabbed report, where the column tabs are each week of a year not monthly!! 🙂 !!!!this article is exactly what i was looking for, something that creates those dynamic cross tabs without ANY cursors, temp tables or global temp tables...

I was able to create the weekly tabs with some minor tweaking to your code ...but i must say in addition to learning how to create a cross tabs on the fly , i picked up a few other pointers-

1] Creating the 'test' table and tally table- something that i will use to create test data in future..

2] Some useful pointers for query performance, like using a select to assign a variable..

3] Using the cube and rollup functions

4]Using a select and coalesce to concatenate multiple columns

But most importantly- how to break up the problem statement into little chunks--- i.e. creating the 'static' portion first and then 'converting ' into the dynamic portion using variables...

much appreciated and i hope to keep learning more..!!!!

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 10

Thank you for the incredible feedback, Pac123. It looks like you've certainly hit all the high points. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eq2home SSC Enthusiast Points: 170 More actions · Answer 11

Super explanation and it clarifies and identifies a simpler and faster approach to building crosstabs. My hat is tipped to you Jeff - Pick clarity of thought, writing ability and an eye for logic and you are lucky to find one existing in a single person. It is extraordinary to find all three in a single individual.

You saved me mucho time, frustration and efforts - Thanks.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 12

eq2home (6/11/2010)
Super explanation and it clarifies and identifies a simpler and faster approach to building crosstabs. My hat is tipped to you Jeff - Pick clarity of thought, writing ability and an eye for logic and you are lucky to find one existing in a single person. It is extraordinary to find all three in a single individual.
You saved me mucho time, frustration and efforts - Thanks.

Wow. Thanks for the awesome compliments. :blush: Glad I could help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eq2home SSC Enthusiast Points: 170 More actions · Answer 13

Here is what I found out today from TechNet (Notice "the will be removed" language:

WITH CUBE

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.

The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.

WITH ROLLUP

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 14

eq2home (6/24/2010)
Here is what I found out today from TechNet (Notice "the will be removed" language:
WITH CUBE
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.
WITH ROLLUP
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.

Have you got a URL for that? If they actually remove it, I'm going to be pretty well ticked off because it's incredibly useful and very fast.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 15

Jeff Moden (6/25/2010)
eq2home (6/24/2010)
Here is what I found out today from TechNet (Notice "the will be removed" language:
WITH CUBE
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.
WITH ROLLUP
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.
Have you got a URL for that? If they actually remove it, I'm going to be pretty well ticked off because it's incredibly useful and very fast.

It's in the 2008 BOL. It looks like "WITH CUBE" is being replace with CUBE(), and "WITH ROLLUP" is being replaced with "ROLLUP()", though it doesn't seem to specifically state that.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2