Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • Good one Jeff.

  • 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)
    Intro to Tally Tables and Functions

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

  • 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)
    Intro to Tally Tables and Functions

  • great article... thank you 🙂

  • 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)
    Intro to Tally Tables and Functions

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

  • 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)
    Intro to Tally Tables and Functions

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

  • 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)
    Intro to Tally Tables and Functions

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

  • 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)
    Intro to Tally Tables and Functions

  • 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

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

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