PIVOT Me

  • steve.jacobs

    SSCommitted

    Points: 1830

    Comments posted to this topic are about the item PIVOT Me

  • Koen Verbeeck

    SSC Guru

    Points: 258870

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Koen Verbeeck (11/17/2013)


    Nice question, thanks.

    +1

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • sqlnaive

    SSCoach

    Points: 17435

    Good QOTD for Monday. 🙂

  • Bangla

    Hall of Fame

    Points: 3137

    Nice one....Thanks....

  • Anipaul

    SSC-Insane

    Points: 24681

    Good question on Pivot Table basics....

  • tom.w.brannon

    Hall of Fame

    Points: 3946

    I am not sure what the point of the pivot was. The result was the same as select distinct a from #temp. The ordering logic does not appear important for basic logic. What am I missing?

  • Sean Lange

    SSC Guru

    Points: 286391

    Technically speaking there are 2 possible correct answers.

    E,D,C,B,A,NULL is also a valid answer. The only ordering going on is to sort NULL to the end. The order of the non null values is undefined so technically there is no order to them.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This was removed by the editor as SPAM

  • Sean Lange

    SSC Guru

    Points: 286391

    Stewart "Arturius" Campbell (11/18/2013)


    Sean Lange (11/18/2013)


    Technically speaking there are 2 possible correct answers.

    E,D,C,B,A,NULL is also a valid answer. The only ordering going on is to sort NULL to the end. The order of the non null values is undefined so technically there is no order to them.

    Agree - i suspect some assumptions were made regarding how SQL Server returns data sets - haven't we had this type of discussion numerous times before?:rolleyes:

    More times than we like to admit....yet we still see it over and over and over...

    That beside, we do not see many questions regarding PIVOT, so, all in all, not a bad question.

    Agreed. While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Revenant

    SSC-Forever

    Points: 42468

    Sean Lange (11/18/2013)


    . . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    I am not sure what is your point, Sean.

    Steve, thanks for an interesting QotD!

  • Sean Lange

    SSC Guru

    Points: 286391

    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    . . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    I am not sure what is your point, Sean.

    Steve, thanks for an interesting QotD!

    The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.

    select a

    from #temp

    group by a

    ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Revenant

    SSC-Forever

    Points: 42468

    Sean Lange (11/18/2013)


    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    . . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    I am not sure what is your point, Sean.

    Steve, thanks for an interesting QotD!

    The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.

    select a

    from #temp

    group by a

    ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END

    I think it was that 'unable' that threw me off.

  • Sean Lange

    SSC Guru

    Points: 286391

    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    . . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    I am not sure what is your point, Sean.

    Steve, thanks for an interesting QotD!

    The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.

    select a

    from #temp

    group by a

    ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END

    I think it was that 'unable' that threw me off.

    LOL. I feel that Jeff's pork chop launcher is aimed at me. I meant "able" not "unable". :blush:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Revenant

    SSC-Forever

    Points: 42468

    Sean Lange (11/18/2013)


    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    Revenant (11/18/2013)


    Sean Lange (11/18/2013)


    . . . While the pivot seems a bit contrived here it does a good job of demonstrating how it can be used. It seems to be difficult to find a usage for PIVOT when another methodology is unable to produce the same results more efficiently.

    I am not sure what is your point, Sean.

    Steve, thanks for an interesting QotD!

    The question does test your knowledge of how PIVOT works. I was merely trying to say that most of the time when I see PIVOT used there are other ways of getting the same results in a more efficient manner. As a result, I rarely use PIVOT at all in the real world.

    select a

    from #temp

    group by a

    ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END

    I think it was that 'unable' that threw me off.

    LOL. I feel that Jeff's pork chop launcher is aimed at me. I meant "able" not "unable". :blush:

    No problem, on Monday mornings I need at least two cups of coffee before I am able to act more or less as is expected. 🙂

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

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