Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Post reply

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Jeff Moden

SSC Guru

Points: 1004307
More actions
December 2, 2008 at 10:15 pm

#196454

Comments posted to this topic are about the item Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
PLEASE READ ME!
This article is an old one and I've been remiss in not updating it but you need to be aware of something... The creation of the dynamic SELECT list will usually (which is not good enough) work ok as posted but, over time, it's been demonstrated many times that it can go awry and return anything from NULL, to nothing, to just a partial result. I need to update the code to use the much safer "FOR XML PATH()" method for folks that have SQL Server 2016 or less and the STRING_AGG() method for those that have 2017 or better.

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

Viewing 15 posts - 1 through 15 (of 130 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Another masterpiece.

Small typo -

You have a single quote missing at the end of the date for the code under OLD fashioned swap.

"SET @EndDate = '2008-01-15 "

"Keep Trying"

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

Chirag (12/3/2008)
Another masterpiece.
Small typo -
You have a single quote missing at the end of the date for the code under OLD fashioned swap.
"SET @EndDate = '2008-01-15 "

Thanks Chirag. I don't know how the heck I do that... I'm real careful about testing the code before I put it in the article, but I guess I haven't mastered the art of copy'n'paste, yet. :blush: As much as I reread these things before I submit them, I also miss the occasional truncation of a word or the misspelling of "an" as "and" or have an out-of-place "the" in the text. I've found a couple of such errors, unfortunately, after the article was published. Guess I can't add "proof reader" to the resume.

Thanks again for the catch.

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Jeff excellent. Before reading this I was going through the discussion of your part 1 article. As I said before I learnt lot from your discussions also. 🙂 This is awesome series.

🙂

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

Thanks for the great feedback Anirban. Great to know that folks get things out of these 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)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Another Great article Jeff. Very informative and useful.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

Great article Jeff!

Do you think this will perform better than a dynamic pivot?

Paul DB SSC Eights! Points: 870 More actions · Answer 7

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. 🙂

Paul DB

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 8

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!

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

ggraber (12/3/2008)
Great article Jeff!
Do you think this will perform better than a dynamic pivot?

Thanks for the feedback and absolutely! 🙂

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Top work, Jeff. You have a rare talent for mixing informal narrative with a technically challenging subject - and with ruthless precision 😎

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mtassin SSC-Insane Points: 23099 More actions · Answer 11

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

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mlandry SSC Veteran Points: 283 More actions · Answer 12

Jeff,

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.

Mark Landry

Tampa, FL

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

mlandry (12/3/2008)
Jeff,
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.
Mark Landry
Tampa, FL

Thanks, Mark... yep... I absolutely agree with everything you said. But, it is a bit more difficult to setup Analysis Services and learn the MDX language than it is to learn how to do a simple cross-tab. Lot's of folks/shops just won't go through it. Not saying that's right or wrong, but simply a fact based on the number of requests for help on cross-tabs on these forums in the last 12 months or so. I figured that if they're going to write a cross tab, they might as well learn how to do it without a cursor or While loop. 🙂

It just wasn't designed for this.

Heh... T-SQL wasn't designed for most of what I do with it... 😛

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

mtassin SSC-Insane Points: 23099 More actions · Answer 14

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]