August 24, 2014 at 4:58 am
One of those awkward questions that requires a guess as to what the question's author meant. And getting that guess right is far more difficult than the technical content of the question.
The first option might be interpreted as offering 0 as an answer, which would be correct if "aggregates" in the question actually meant "aggregates", but that would be a poor interpretation since the whole point of the pivot clause is to generate (lots of) aggregates and the phrasing of the first option seems to me to exclude that. I guess the people who picked that option were just saying that "works with" means something rather stronger than "produces" so that the first option would be correct; without reading Steve's mind, they were just as likely to be right as not. None of the other options works if "aggregates" means "aggregates" because they are all non-zero, unless "in the clause" in the question doesn't really mean "in the clause".
But some people might guess that "aggregates" really does mean aggregates, so that "in the clause" has to mean something like "in the output generated with the clause" (if the 0 implied by the first option can't be used), in which case the answer is whatever the product of the two column list lengths is (subtracting 1 from one of them), and then guess that there is some restriction on those lengths in which case the highest numbered offered as an answer might be the most likely, and the second highest is a possibility. I suspect that that is why currently 55% of people answering so far have picked one of those two incorrect answers, and only 22% have picked the right answer.
I reckoned that 128 is so small a number that that last possible interpretation couldn't possibly be correct: if some number large enough to look like a physical storage limit had been offered I might have gone for that, but no such big number was offered. That left me only one option to choose, and that means that "aggregates" in the question means "aggregate functions" (and "in the clause" actually does mean "in the clause"). By luck that guess at what the question meant was right. But it was luck - maybe the odds were on my side because it assumes a simple mis-phrasing, whereas the interpretations leading to other options assume something messier (and two of them are downright nonsense anyway, but once one asumes the question doesn't mean what it says I guess anything goes) but it was still just a guess.
Tom
August 24, 2014 at 5:33 am
Since you wrote such a long post Tom, I guess you really got it wrong:-P
😎
BTW thanks for the question Steve.
August 24, 2014 at 12:36 pm
Eirikur Eiriksson (8/24/2014)
Since you wrote such a long post Tom, I guess you really got it wrong:-P😎
I get verbose when I'm in pedantic :alien: mode, and poor wording in QotD generally puts me there (except when i'm responsible for the poor wording, of course :hehe: ). When I go pedantic I tend to get things right more often than that. Overall I get about 20% QotD wrong :angry: (wish I could get it down to 2% :w00t: ) but I do much better than that in pedantic mode.
BTW thanks for the question Steve.
I forgot to say that, didn't I. :blush:
Tom
August 24, 2014 at 1:58 pm
Added "functions" to the QotD, though I think you're being a bit pedantic, Tom.
August 24, 2014 at 2:37 pm
Steve Jones - SSC Editor (8/24/2014)
Added "functions" to the QotD, though I think you're being a bit pedantic, Tom.
That's an understatement if I ever seen one:-D
😎
August 25, 2014 at 12:32 am
Nice question Steve, thanks 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 25, 2014 at 1:35 am
Nice question Steve, Thanks for sharing !!!
August 25, 2014 at 3:21 am
frankly, the answer is straight forward as "one" ,but I unnecessarily over looked into the question and thought, author mentioned "single pivot", so I thought using unions we can use multiple aggregate functions and answered, eight considering all aggregate functions (avg,min,max,count,sum... etc.,) :w00t::hehe:
got it wrong :sick:
August 25, 2014 at 4:30 am
Thank you for the post, Steve, good one.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
August 25, 2014 at 4:35 am
TomThomson (8/24/2014)
...............
Thank you, Tom. As they say, there is no good or bad, and it is just a person's point of view. Not just on in this post, not SQL.. but in general having such insights and knowledge about subject is very very useful and also improves the analytical skills, which I lack to a greater length, and this is how I learn. Always great to read your comments, Thanks again. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
August 25, 2014 at 6:22 am
Nice question, so thanks for it.
August 25, 2014 at 6:51 am
Does anyone actually use the PIVOT operator?
I find it so limited that I almost always roll my own using GROUP BY and CASE.
August 25, 2014 at 7:03 am
sestell1 (8/25/2014)
Does anyone actually use the PIVOT operator?I find it so limited that I almost always roll my own using GROUP BY and CASE.
I have one SP where I use it to generate the data (raw), it gives 600 to 950 rows, but when pivoted it spreads across 23 cols and 45 rows, building the pivot cols dynamically and passing the SPs data to it I get the data as I want to represent it in the grid(FE) (instead of passing just the data to the FE, I pivot it and then send it, rather Pivoting at the FE, I guess that's too much coding. As of now the data is less so it takes less than 3 seconds for the SP to execute in the SQL server. I need to see if the data increases heavily then what will be the execution time, or possibly improve the performance of the PIVOT or find an alternate method for client-side pivoting.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
August 25, 2014 at 7:08 am
Raghavendra Mudugal (8/25/2014)
sestell1 (8/25/2014)
Does anyone actually use the PIVOT operator?I find it so limited that I almost always roll my own using GROUP BY and CASE.
I have one SP where I use it to generate the data (raw), it gives 600 to 950 rows, but when pivoted it spreads across 23 cols and 45 rows, building the pivot cols dynamically and passing the SPs data to it I get the data as I want to represent it in the grid(FE) (instead of passing just the data to the FE, I pivot it and then send it, rather Pivoting at the FE, I guess that's too much coding. As of now the data is less so it takes less than 3 seconds for the SP to execute in the SQL server. I need to see if the data increases heavily then what will be the execution time, or possibly improve the performance of the PIVOT or find an alternate method for client-side pivoting.
In my experience, CROSSTAB takes less of a penalty for increased number of columns than PIVOT, apart from allowing for different aggregation on the columns. It can be built dynamically just as the PIVOT statement.
😎
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply