Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 15 posts - 16 through 30 (of 130 total)

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

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 1

Thank you for your efforts here Jeff. An article of this level takes hours to prepare, and the effort shows. Although I am an Analysis Server and Report Server convert, I still think this kind of TSQL workup is useful for instruction on many levels, and is the type of project that demonstrates to the less informed that server-side TSQL is far more than just CRUD (A.K.A. READ or SUID). I love dynamic code projects. I dislike static hard-coded unyeiding inflexible and dead-when-deployed code.

You are very spot-on when you say TSQL allows you to do many things not originally designed (but powerful and useful just the same).

WayneS SSC Guru Points: 95460 More actions · Answer 2

Jeff Moden (12/3/2008)
Heh... T-SQL wasn't designed for most of what I do with it... 😛

Isn't that the truth.

Fantastic article! Thanks!

Wayne

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

jcraddock Default port Points: 1498 More actions · Answer 3

I've had better luck writing code that writes my SQL. I wrote a set of code that you point at any table, it accepts any column within the table as the top or left portion of the crosstab and any numeric column as the data portion. Another option is to calculate as percent of the whole or sum or count.

Doing it that way, you have one set of reusable code for all crosstabs...works pretty well. I usually just create a simple view, point the object at the view and Voila have instant reconfigurable, groupable by anything crosstab. I even added an option to subgroup on the left side.

Same concept, more code up front, but infinitely reusable.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Great article, Jeff. Like The Return of the King, it was well worth the wait!

🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

mlandry (12/3/2008)
Whoa, using SQL to do cross-tabs really is "old school" (circa mid-90's or before.) It just wasn't designed for this.
Do yourself a BIG favor and try Analysis Services. The MDX language is everything the SQL "select-groupby" ever wanted to be when it grew up. And it's far more expressive in terms of business reporting than SQL will ever be.

I am sure that a similar article on how to use Analysis Services/MDX to do this for those of us who are T-SQL wonks only, would be very well received.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SoCal_DBD Hall of Fame Points: 3382 More actions · Answer 6

I have been getting my daily SQLServerCentral.com emails and ashamedly skipping by them lately. I just had other stuff going on and hadn't lurked here in a while. But when I saw the subject, "Cross Tabs and Pivots, Part 2", I thought to myself..... that's gotta be something from Jeff. I HAVE to read that! As usual, pure poetry. It reconfirmed much of what I already do and taught me a few new tricks in the process. Thank you for taking the time to be so thorough.

Lisa

Nicole Bowman SSCommitted Points: 1555 More actions · Answer 7

Jeff, thanks for another great article. Your articles are easy to understand and fun to read. I too would like an article on Analysis Services. I haven't used it and would like an article that I can understand. Pretty please would you write one?

Cheers!

Nicole Bowman

Nothing is forever.

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

Luke L (12/3/2008)
Another Great article Jeff. Very informative and useful.
-Luke.

Thanks, Luke. I 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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Paul DB (12/3/2008)
I also enjoyed the article. I like that you include applications and explanations of best some practices, e.g. the discussion on using a SELECT instead of an IF under the Variables for the Start and End Dates section. It is quite helpful that the article is linked to other articles for more basic information. I also like that you distinguish between a best practice and a my-preference.
Thanks. 🙂

Thanks for the great feedback, Paul. Not sure my method of using a SELECT on the date swap is what most people would consider to be a best practice... I get a lot of flack about overwriting variables like that because folks say it's not documented. Technically, they're correct... it's not documented in SELECT, but it's "implied" in the BOL documentation under UPDATE for the "quirky" update method of SET @variable = columnname = expression. The method I used can't be done in other RDBMS's such as Oracle. All part of why I love SQL Server...

I try not to force best practices on anyone because lot's of folks have their own idea of what a best practice is or not... I just try to explain what will happen if you use my methods or not. Like I said, some of my "best practices" drive ANSI compliant zealots absolutely bonkers. 😀 Shoot, I've already been advised in this thread that SQL Server wasn't designed to do cross-tabs... guess I'll have to change the title to "Bumble Bee" code because the Bumble Bee doesn't know it's not designed to fly, either. :hehe:

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

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

Carla Wilson (12/3/2008)
Awesome! Having spent years using all sorts of languages to prepare cross-tabs, I really love the results here.
I, too, went back and read the discussion on the first article. Actually, my main reason for this post is so I get emailed on this discussion! 🙂
Thanks, Jeff!

You're welcome, Carla... thanks for the feedback. Hmmmm... since you've spent all that time in different languages to prep cross-tabs, let me ask... what do you think of OLAP (Analysis Services) in SQL Server?

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Chris Morris (12/3/2008)
Top work, Jeff. You have a rare talent for mixing informal narrative with a technically challenging subject - and with ruthless precision 😎
Cheers
ChrisM

Now, there's a tag line I may have to include on my resume! :w00t: That's an awsome compliment, Chris... Thank you for 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)

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

mtassin (12/3/2008)
That was just an amazing read... learned some neat tricks that I didn't even think were possible (but in retrospect should have known)... very informative...

Thanks for the great feedback, Mark. Heh... I didn't actually realize how many extra "tricks" I'd thrown into that article until someone told me the article took 16 type-written pages to print out!

Anyway, very happy you enjoyed it. Keep that "No RBAR" flag flying! (Take a close look at Mark's avatar, folks).

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

mtassin (12/3/2008)
My favorite way to do cross-tabs is to cheat..
Put the data into a cross tab friend format (measures and values to break it out by)
and feed it into crystal reports.

Man, do I agree with that! My favorite way to do anything in code is to cheat! 😛

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

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

dphillips (12/3/2008)
Thank you for your efforts here Jeff. An article of this level takes hours to prepare, and the effort shows. Although I am an Analysis Server and Report Server convert, I still think this kind of TSQL workup is useful for instruction on many levels, and is the type of project that demonstrates to the less informed that server-side TSQL is far more than just CRUD (A.K.A. READ or SUID). I love dynamic code projects. I dislike static hard-coded unyeiding inflexible and dead-when-deployed code.
You are very spot-on when you say TSQL allows you to do many things not originally designed (but powerful and useful just the same).

Thank you for the wonderful recognition! It was a bit like writting a chapter in a book and I really appreciate it when someone gives this kind of feedback.

I agree... there's a heck of a lot that can be done in T-SQL. Tony Davis recently posted an editorial on [font="Arial Black"]The Full Potential of SQL 2000[/font][/url] in which he concludes with the question, "Have I even got close to exploiting the full potential of SQL Server 2000?" My answer to that question would be a resounding "NO, most people haven't"... and, maybe surprisingly, I include myself in that group.

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

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

jcraddock (12/3/2008)
I've had better luck writing code that writes my SQL. I wrote a set of code that you point at any table, it accepts any column within the table as the top or left portion of the crosstab and any numeric column as the data portion. Another option is to calculate as percent of the whole or sum or count.
Doing it that way, you have one set of reusable code for all crosstabs...works pretty well. I usually just create a simple view, point the object at the view and Voila have instant reconfigurable, groupable by anything crosstab. I even added an option to subgroup on the left side.
Same concept, more code up front, but infinitely reusable.

I absolutely agree with that, Jim. I was going to try to write some code to do such a thing as you describe, but you beat me to it... probably by years.

I think folks would give up an eye-tooth to see such code. You haven't written an article since February of 2005... maybe it's time to put pen to paper again. It sounds like the "Rosetta Stone" for all dynamic cross-tabs and I know that I'd seriously like to see that code in an 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)