PIVOT Me
-
Comments posted to this topic are about the item PIVOT Me
-
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (11/17/2013)
Nice question, thanks.+1
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Good QOTD for Monday. 🙂
-
Nice one....Thanks....
-
Good question on Pivot Table basics....
-
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?
-
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
-
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/ -
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!
-
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/ -
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.
-
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/ -
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 28 total)
You must be logged in to reply to this topic. Login to reply