Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

  • Jeff Moden

    SSC Guru

    Points: 996847

    Comments posted to this topic are about the item [font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

    Folks, Part 2 of this article can be found at the following link...

    [font="Arial Black"](click here) Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/font][/url]

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Phil Factor

    SSC-Insane

    Points: 20084

    [p]I hope I'm not mentioning anything that you are covering in subsequent articles, but when you need row and column totals, averages, or other aggregations, then suddenly the CUBE and ROLLUP operators start to become very useful. To take your example....[/p]

    [font="Courier New"]

    SELECT

       COALESCE(CONVERT(CHAR(4),YEAR),'Sum'),

       STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],

       STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],

       STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],

       STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],

            STR(SUM(Amount),5,1) AS Total

    FROM #SomeTable1

    GROUP BY YEAR WITH ROLLUP ORDER BY GROUPING(YEAR)

    /*

         1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total

    ---- ------- ------- ------- ------- -----

    2006   1.1     1.2     1.3     1.4     5.0

    2007   2.1     2.2     2.3     2.4     9.0

    2008   1.5     0.0     2.3     1.9     5.7

    Sum    4.7     3.4     5.9     5.7    19.7

    */

    [/font]

    [p] I love crosstabs and pivot-tables. Soothing, they are, like knitting.[/p]

    Best wishes,
    Phil Factor
    Simple Talk

  • Ian Gibson-270409

    SSC Enthusiast

    Points: 133

    That's interesting. I had my doubts as to how useful pivot might be. It's good to see it backed up by examples and statistics.

    I'm looking forward to what you have to say about dynamic cross tabs. I recently had to do a cross tab for an electronic voting system using proportional representation where each election can have a different number of candidates. The only solution I could come up with in the time available works but is so complicated as to be ridiculous.

  • Peter Smith-247883

    SSC Enthusiast

    Points: 100

    Jeff Moden (8/18/2008)


    Comments posted to this topic are about the item

    What a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for SQL proper to catch up. About to convert an Access db to a SQL Express back-end. I imagine the X-tab queries will be a major nightmare.

    pj

  • Jeff Moden

    SSC Guru

    Points: 996847

    Phil Factor (8/19/2008)


    [p]I hope I'm not mentioning anything that you are covering in subsequent articles, but when you need row and column totals, averages, or other aggregations, then suddenly the CUBE and ROLLUP operators start to become very useful. To take your example....[/p]

    Actually, you did, Phil... I'm devestated... 😉

    True enough... that will be in a subsequent article, but you've stolen no thunder. Thank you for the feedback ol' friend.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996847

    Peter Smith (8/19/2008)


    Jeff Moden (8/18/2008)


    Comments posted to this topic are about the item

    What a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for SQL proper to catch up. About to convert an Access db to a SQL Express back-end. I imagine the X-tab queries will be a major nightmare.

    pj

    I agree... thanks for the feedback, Peter. You'd think that if MS wanted to make it easier, they'd have come up with something a lot easier than Pivot syntax. What would be interesting would be to do a million row test on manual cross-tab vs the automatic cross-tab functionality in Access. If it's done right, the automatic cross-tab should should win by a significant margin.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Peter Smith-247883

    SSC Enthusiast

    Points: 100

    Jeff, I've copied your code. Maybe it will reduce the bad language as the upscaling to SQL takes place. Thanks for the article.

  • ChiragNS

    One Orange Chip

    Points: 26137

    gr8 article Jeff. Keep them coming.

    "Keep Trying"

  • Paul DB

    SSC Eights!

    Points: 840

    That was an amazing article. :w00t: Thanks so much, Jeff.

    Paul DB

  • Jack Corbett

    SSC Guru

    Points: 184381

    Hey Jeff, good article. It's funny I have answered several forum questions recently using a Case embedded in an aggregate. They weren't crosstabs exactly, but similar. So, after having answered these questions I decided to post it on my blog, hoping someone might stumble upon it on a search. I was even considering submitting an article to SSC on it, but you beat me to the punch. At least the info is out there and with much more performance testing done on it than I would have done. Looking forward to the rest of the series.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Really nice to read, Jeff, and absolutely spot on.

    I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed) columns with unknown names, as in the following. It will be interesting to see your solution, you have a knack of coming up with "why the heck didn't I think of that?" solutions!

    Cheers

    ChrisM

    SET NOCOUNT ON

    CREATE TABLE #EDIInvoices (HospitalName VARCHAR(20), Purchaser VARCHAR(20), Sales money)

    INSERT INTO #EDIInvoices (HospitalName, Purchaser, Sales)

    SELECT 'Guy''s', 'BUPA', 10001 UNION ALL

    SELECT 'Guy''s', 'Microsoft', 10002 UNION ALL

    SELECT 'Guy''s', 'NIH', 10003 UNION ALL

    SELECT 'Tommy''s', 'BUPA', 20001 UNION ALL

    SELECT 'Tommy''s', 'Microsoft', 20002 UNION ALL

    SELECT 'Tommy''s', 'NIH', 20003 UNION ALL

    SELECT 'Tommy''s', 'Cornhill', 20004 UNION ALL

    SELECT 'Raigmore', 'BUPA', 30001 UNION ALL

    SELECT 'Raigmore', 'Microsoft', 30002 UNION ALL

    SELECT 'Raigmore', 'NIH', 30003 UNION ALL

    SELECT 'Raigmore', 'Cornhill', 30004 UNION ALL

    SELECT 'Raigmore', 'HPA', 30005 UNION ALL

    SELECT 'Raigmore', 'RaigmoreOnly', 30006 UNION ALL

    SELECT 'Yorkhill', 'BUPA', 40001 UNION ALL

    SELECT 'Yorkhill', 'Microsoft', 40002 UNION ALL

    SELECT 'Yorkhill', 'Cornhill', 40004 UNION ALL

    SELECT 'Yorkhill', 'HPA', 40005 UNION ALL

    SELECT 'Yorkhill', 'YorkhillOnly', 40007

    --SELECT * FROM #EDIInvoices -- Sanity check

    -- PIVOT the results

    DECLARE @SQLstr VARCHAR(5000)

    SET @SQLstr = ''

    SELECT @SQLstr = @SQLstr + CHAR(10) + ' MAX(CASE Purchaser WHEN ''' + Purchaser + ''' THEN Sales ELSE 0 END) AS [' + Purchaser + '], '

    FROM (SELECT Purchaser FROM #EDIInvoices GROUP BY Purchaser) d

    SET @SQLstr = 'SELECT HospitalName, ' + REVERSE(STUFF(REVERSE(@SQLstr),1,2,'')) + ' ' + CHAR(10) + 'INTO ##EDIInvoicesPivot FROM #EDIInvoices GROUP BY HospitalName'

    PRINT @SQLstr -- sanity check

    EXECUTE (@SQLstr)

    SELECT * FROM ##EDIInvoicesPivot

    DROP TABLE #EDIInvoices

    DROP TABLE ##EDIInvoicesPivot

    [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

  • sheila.clancy

    SSC Rookie

    Points: 35

    Check out this article on Dynamic Crosstabs by Jeff Smith:

    http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

    -smc

  • marty.seed

    SSCrazy

    Points: 2441

    I am interested as to how or if we can pull a cross-tab or pivot dynamically. I have a query that can have an undetermined amount of comments associated to one job. In the query I need to show these comments in one line.

    SET NOCOUNT ON

    CREATE TABLE #BidComments (BidId int, Comment VARCHAR(varchar(max))

    INSERT INTO #BidComments (BidId , Comment)

    SELECT 1, 'This is comment 1 for bid 1' UNION ALL

    SELECT 1, 'This is comment 2 for bid 1' UNION ALL

    SELECT 1, 'This is comment 3 for bid 1' UNION ALL

    SELECT 2, 'This is comment 1 for bid 2' UNION ALL

    SELECT 2, 'This is the second comment for bid 2' UNION ALL

    SELECT 2, 'Here we have the third comment for bid2' UNION ALL

    SELECT 2, 'Comment 4' UNION ALL

    SELECT 3, '1 comment on 3' UNION ALL

    SELECT 3, 'comments for 3, the second' UNION ALL

    SELECT 3, 'comment 3' UNION ALL

    SELECT 3, 'Comment 4' UNION ALL

    SELECT 3, 'Comment 5' UNION ALL

    In the end I need to display it as

    BidID Comment1 Comment2 Comment3

    Any thoughts?

  • gerhard-356547

    SSC Rookie

    Points: 41

    Congratulations on a well-written and informative article.

  • Charles Kincaid

    SSChampion

    Points: 13593

    Great article Jeff. Keep 'em coming.

    ATBCharles Kincaid

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

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