Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

  • Greg Larsen

    SSC-Insane

    Points: 20585

    Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

    Gregory A. Larsen, MVP

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the PIVOT training.

  • Toby Harman

    SSCarpal Tunnel

    Points: 4124

    I appreciate that this is an example for an exam, and learning how to PIVOT (and UNPIVOT) is important.

    I haven't redone the performance testing recently, but from memory there was a performance issue with pivot which meant that you were actually better off doing a CASE statement to get the same values. Is that still valid, or have they fixed PIVOT?

    So your query becomes something like this:

    DECLARE

    @StartDate DATE

    , @EndDate DATE

    SET @StartDate = '2005-01-01'

    SET @EndDate = '2006-12-31'

    SELECT

    YEAR(OrderDate) AS OrderYear

    , TerritoryID

    , SUM(CASE WHEN MONTH(OrderDate) BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS [1]

    , SUM(CASE WHEN MONTH(OrderDate) BETWEEN 4 AND 6 THEN 1 ELSE 0 END) AS [2]

    , SUM(CASE WHEN MONTH(OrderDate) BETWEEN 7 AND 9 THEN 1 ELSE 0 END) AS [3]

    , SUM(CASE WHEN MONTH(OrderDate) BETWEEN 10 AND 12 THEN 1 ELSE 0 END) AS [4]

    FROM Sales.SalesOrderHeader

    WHERE OrderDate >= @StartDate

    AND OrderDate <= @EndDate

    GROUP BY YEAR(OrderDate), TerritoryId

    ORDER BY YEAR(OrderDate), TerritoryId

  • valeryk2000

    SSCarpal Tunnel

    Points: 4237

    Toby: I've the same experience - SUM(Case ... works faster than PIVOT

  • paolo.bert

    SSC Enthusiast

    Points: 149

    I am not a PRO, but I used them in Access97, writing SQL code (TRANSFORM command), a long time ago, and as far as I remember there wasn't that burden of work to have dynamic columns... Is there any advantage in SQL Server?

  • kbenton 6603

    SSC Veteran

    Points: 234

    Thanks for the excellent tutorial. I've had to create numerous pivot tables over the years and it was a struggle learning them initially. You've laid out easy to follow examples and bonus...included the dynamic pivoting. It's terrific having both examples explained so well together in one article.

  • Jeff Moden

    SSC Guru

    Points: 993770

    paolo.bert (9/23/2016)


    I am not a PRO, but I used them in Access97, writing SQL code (TRANSFORM command), a long time ago, and as far as I remember there wasn't that burden of work to have dynamic columns... Is there any advantage in SQL Server?

    The PIVOT operator in Access blows the doors off the PIVOT operator in SQL Server in just about every aspect. I was amazed, disappointed, and frustrated at how crippled and relatively difficult it is to use compared to what is in Access. Why Microsoft didn't steal their own code to make it as robust as that found in Access, I'll never know.

    There's a relatively "ancient" technique of pivoting data that seems easier (to me, anyway) to understand, doesn't have the performance problems of the PIVOT operator, and is more flexible when it comes to things like adding line-end totals, adding "bottom line" and sub totals, displaying a "0" instead of a NULL for elements with no data, building multiple-pivoted columns (ex. Qty and $ amounts in the same report) and in building dynamic "rolling date" reports. It's called a CROSS TAB. Don't look for it in any of the newer versions of "Books Online" because MS removed it.

    CROSS TAB isn't an operator in SQL Server. It's the name of a technique that's becoming a lost art and proof of the old adage that "Change is inevitable... change for the better is not". Combined with "Pre-Aggregation" techniques (thank you Peter "Peso" Larsson for the term), CROSS TABs can be roughly twice as fast as PIVOTs even if you apply the same pre-aggregation techniques to a PIVOT operator.

    For an introduction to the CROSS TAB technique and pre-aggregation and includes performance testing (although a bit dated now), please see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    For an introduction in how to make automatic "rolling date" pivots using dynamic SQL along with the CROSS TAB technique, please see the following article.

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

    Bottom line for me is that I don't use the PIVOT operator in SQL Server.

    Still, even the CROSS TAB technique pales in comparison to the PIVOT operator found in ACCESS.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • paolo.bert

    SSC Enthusiast

    Points: 149

    What to say, Jeff... First of all, maaany thanks for your thorough reply. I was afraid to post a so 'stupid' question on this Guru's cave, but now I am confortable to see that I am not the only one who find this implementation quite strange... Thanks again! 🙂

  • Jeff Moden

    SSC Guru

    Points: 993770

    paolo.bert (9/23/2016)


    What to say, Jeff... First of all, maaany thanks for your thorough reply. I was afraid to post a so 'stupid' question on this Guru's cave, but now I am confortable to see that I am not the only one who find this implementation quite strange... Thanks again! 🙂

    My pleasure and thank you for the feedback, Paolo.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • h.tobisch

    SSCommitted

    Points: 1661

    I do not like the wording. A column HAS a name and CONTAINS values.

    so, to my understanding,

    'first pivoted column' should be 'first value of pivoted column'

  • Jeff Moden

    SSC Guru

    Points: 993770

    h.tobisch (9/23/2016)


    I do not like the wording. A column HAS a name and CONTAINS values.

    so, to my understanding,

    'first pivoted column' should be 'first value of pivoted column'

    Since the author is talking about a column and not just the first value in the column, I have to disagree with your disagreement.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Jeff Moden (9/23/2016)


    paolo.bert (9/23/2016)


    For an introduction to the CROSS TAB technique and pre-aggregation and includes performance testing (although a bit dated now), please see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    For an introduction in how to make automatic "rolling date" pivots using dynamic SQL along with the CROSS TAB technique, please see the following article.

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

    Thanks for the tips and links.

  • h.tobisch

    SSCommitted

    Points: 1661

    well, whatever:

    pivot column or column being pivoted refers (to my understanding, which you are invited to correct)

    to the original column containing the values, whereas the columns resulting from the values in that column

    might be referred to by ,say, 'pivot result column'

  • Jeff Moden

    SSC Guru

    Points: 993770

    Jeff Moden (9/23/2016)


    h.tobisch (9/23/2016)


    I do not like the wording. A column HAS a name and CONTAINS values.

    so, to my understanding,

    'first pivoted column' should be 'first value of pivoted column'

    Since the author is talking about a column and not just the first value in the column, I have to disagree with your disagreement.

    My pleasure. Thank you for 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • paolo.bert

    SSC Enthusiast

    Points: 149

    Thanks to All!

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

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