Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 15 posts - 91 through 105 (of 130 total)

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

calston SSC Rookie Points: 33 More actions · Answer 1

Jeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!

Cynthia

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

calston (3/4/2009)
Jeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!
Cynthia

That's outstanding news, Cynthia!! Glad to have been a help. Thanks for taking the time to post this wonderful feedback... it's very much appreciated!

--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)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 3

Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work!

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

Manie Verster (4/18/2009)
Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work!

Thanks for the great feedback, Manie.

Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.

Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".

I'll be sure to include all code in a handy text file in future articles.

--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)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 5

Jeff Moden (4/20/2009)
Manie Verster (4/18/2009)
Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work!
Thanks for the great feedback, Manie.
Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.
Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".
I'll be sure to include all code in a handy text file in future articles.

Jeff, thanks for the tip and it worked and thanks for a great script and article. You know, a person gets a need for pivots and crosstabs all the time and when you actually create it in the best way you know, it sometimes lack performance. A script like this is just the thing to do these things in a better way.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

Thanks, Manie. I sure do appreciate 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)

ray-588024 SSC Rookie Points: 25 More actions · Answer 7

Excellent post, Jeff. The SQL PIVOT operator has always been a complete mystery to me, and my attempt at it today was no better. I found your solution and like it better because I can understand what I wrote without having to look up PIVOT everytime I want to read my own code!

I especially liked the trick of accumulating the string value for the variable number of columns by using a SELECT instead of a loop. Very cool. 😎

You've made me a better SQL programmer today, and for that, I thank you.

Ray

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

Very cool. Glad you could use so much of the article. Thanks for the feedback, Ray.

--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)

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 9

I saw the title and author of the articles and it seemed like old times. 🙂

Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!

Regards,

Mike M

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

Mike M - DBA2B (2/25/2010)
I saw the title and author of the articles and it seemed like old times. 🙂
Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!
Regards,
Mike M

This is terrible, Mike... I know about 6 "Mike M"s that I've not seen in several years... which one are you?

--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)

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 11

Ha!

Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.

You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. 😉

Regards,

Mike M

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

Mike M - DBA2B (2/25/2010)
Ha!
Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.
You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. 😉
Regards,
Mike M

Oh my... that WAS a long time ago. That was on the "BELUTION" forum which has been offline for many years... probably about 6 just as you mention. Glad to see you around!

--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)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 13

Jeff Moden (2/25/2010)
Mike M - DBA2B (2/25/2010)
Ha!
Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.
You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. 😉
Regards,
Mike M
Oh my... that WAS a long time ago. That was on the "BELUTION" forum which has been offline for many years... probably about 6 just as you mention. Glad to see you around!

Good Article Jeff

Here are mine

SQL Server 2000 - http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

SQL Server 2005 -

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

When I visited belution forum, I used to think you were the moderator or site owner and I came to know about you only from that forum 🙂

But what happened to it? I wanted to ask about it to you sometimes back

Madhivanan

Failing to plan is Planning to fail

Dennis Wagner-347763 SSC Eights! Points: 945 More actions · Answer 14

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!

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 15

Madhivanan-208264 (4/30/2010)
When I visited belution forum, I used to think you were the moderator or site owner and I came to know about you only from that forum 🙂
But what happened to it? I wanted to ask about it to you sometimes back

I'm not sure what happened to the Belution forum. One day, it just stopped working. I did a search for info about it on the web a while back and there was some little news that someone was thinking of selling it. Not sure what really happened, though.

--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)