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

  • srweal (5/6/2010)


    You are a gun Jeff. Got this tasty article in my inbox and was so glad I stopped by for a read. PIVOT scared me off a few months back and I have been pondering how to get some answers out of my EAV tables.

    This approach seems to get me on the way. Love it.

    Btw, in the conclusion of your Part 1 you mention something in future parts about EAV/NVP. Is that going to be in a forthcoming article, or have I misinterpreted what you were getting at there?

    Thanks for the great feedback. And, no, you're not misinterpreting anything. I did promise another article on EAV/NVP usage and never got to it. I should probably get crackin' on one since I made the promise, huh?

    Nice to see another person who prefers cross tabs to pivots.

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

  • Toby Harman (8/6/2010)


    As an aside (and without reading all 20 pages on this thread) I played around with the PIVOT operator on a SELECT statement and found some interesting performance metrics with large-ish datasets (100,000 rows).

    Thanks for stopping by and thanks for the feedback.

    Shifting gears, what "interesting performance metrics with large-ish datasets" did you find?

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

  • I soooo prefer the CASE syntax to PIVOT! 🙂

    I did learn something new, which honestly doesn't happen all that often for me when it comes to the relational engine - that preaggregating the data and improve pivoting performance especially on larger datasets. Quite interesting!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Dear All,

    This all [HARD coded] like year or month whatever

    in my situations, i don't want to [HARD Coded] my pivt value,

    how to build the qry, if anybody's says welcome.

    Otherwise i will pass it to.

  • Gillbert (8/6/2010)


    Dear All,

    This all [HARD coded] like year or month whatever

    in my situations, i don't want to [HARD Coded] my pivt value,

    how to build the qry, if anybody's says welcome.

    Otherwise i will pass it to.

    No problem. See Part 2 of this series...

    http://www.sqlservercentral.com/articles/Crosstab/65048/

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

  • TheSQLGuru (8/6/2010)


    I soooo prefer the CASE syntax to PIVOT! 🙂

    I did learn something new, which honestly doesn't happen all that often for me when it comes to the relational engine - that preaggregating the data and improve pivoting performance especially on larger datasets. Quite interesting!

    Thanks for the feedback, Kevin. I especially like the fact that you like the CASE syntax of a cross tab.

    Again, I have to give credit to fellow speed-phreak Peter "PESO" Larrson for coining the phrase "Pre-Aggregation". It's useful in places other than cross tabs, as well, but works especially well for such things.

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

  • Thanks for the article Jeff.

  • roger_os (8/6/2010)


    Thanks for the article Jeff.

    You bet. Thanks for stopping by.

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

  • I want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?

  • joel.weiss 70857 (8/6/2010)


    I want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?

    Why are you picking on 'the "hard-core" developers on these forums' and why do you believe there's a "religions ban on thinking"? Before you explain, you might try a bit of an attitude change... a lot of those supposedly limited thinkers might actually be able to help you out. 😉

    You might also try doing a search to find articles about dynamic pivots...

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --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, you are totally right. Must have been a bad breakfast... my apologies. -Joel

  • joel.weiss 70857 (8/6/2010)


    Jeff, you are totally right. Must have been a bad breakfast... my apologies. -Joel

    Thanks for that, Joel. Heh... I've had coffee from a soapy cup before... you're not the only one. 🙂

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

  • Hey Joel,

    Not sure you saw it in my previous post because of the "edit" I did... Check out the following article. While it may not be as intelligent as Access or Excel (I agree... gotta love pivots in both of those), it does bring SQL Server a bit closer... heh... even if it was written by one of those "hard-core" developers. 😛 http://www.sqlservercentral.com/articles/Crosstab/65048/

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

  • I'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as in

    Project Name DeptXEmployees DeptYEmployees DeptZEmployees

    ABC Liquors AH, AW, RT DH MV, EJ, BP, SS, MM, BC

  • Terrific article Jeff, and welcome too. I thought I was being lazy for sticking to the cross tab format for the last few years. I couldn't agree with you more regarding readability, and that important quality shouldn't be ignored.

    Thanks,

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Viewing 15 posts - 196 through 210 (of 243 total)

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