Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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"

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

    πŸ™‚

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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]

  • Great article Jeff!

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

  • 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

  • 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!

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

    Cheers

    ChrisM

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • 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]

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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]

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

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