PIVOT

Question

PIVOT

August 25, 2014 at 10:02 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 32 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Steve Jones - SSC Editor (8/24/2014)
Added "functions" to the QotD, though I think you're being a bit pedantic, Tom.

So do I 🙂

Tom

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

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.

Lots of people come to the forums asking for help with pivot functions. I usually suggest them to use the crosstab approach. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sknox SSChampion Points: 12388 More actions · Answer 3

I was making this a lot harder than it needed to be, until I remembered this part of PIVOT:

..

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

...

Since PIVOT uses data from the row to generate the column names, that left me with three options:

1. PIVOT is a simple function with a 1:1 mapping (one column for every distinct value, meaning one value and therefore one aggregate function :Whistling: )

2. PIVOT allows for the creation of large numbers of columns with duplicate names, or

3. PIVOT uses a complex algorithm to generate column headers for each aggregated column to ensure uniqueness.

Now, "Microsoft" and "complex" often go hand in hand, so #3 was tempting, but I have used PIVOT before, so I knew that wasn't the case.

Duplicating column names is possible, but leads to a mess where the output essentially can't be used in any further queries, so #2 was out.

That left #1, or 1:1 or one.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

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 like PIVOT because you can use it to do a dynamic number of columns on the back end.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

jshahan (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 like PIVOT because you can use it to do a dynamic number of columns on the back end.

You can do that with cross tabs as well. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

Luis Cazares (8/25/2014)
jshahan (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 like PIVOT because you can use it to do a dynamic number of columns on the back end.
You can do that with cross tabs as well. 😀

Hi Luis. It's been a long time since I wrote a cross tab (sql 2005, I think) and it seems like there were significant restrictions on the types of aggregates you could use. Like only being able to use SUM or something. I just tried it on 2008 R2 and it seems like everything but COUNT DISTINCT is working. Maybe I should revisit cross tabs.

SqlMel SSCrazy Points: 2901 More actions · Answer 7

Can't believe I got that wrong.

Thanks for the question, though.

---------------
Mel. 😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

jshahan (8/25/2014)
Luis Cazares (8/25/2014)
jshahan (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 like PIVOT because you can use it to do a dynamic number of columns on the back end.
You can do that with cross tabs as well. 😀
Hi Luis. It's been a long time since I wrote a cross tab (sql 2005, I think) and it seems like there were significant restrictions on the types of aggregates you could use. Like only being able to use SUM or something. I just tried it on 2008 R2 and it seems like everything but COUNT DISTINCT is working. Maybe I should revisit cross tabs.

I've never found any problems with the different aggregates on cross tabs. I just tried COUNT DISTINCT and it works fine.

I've also tested and when you need more than a single aggregate, either multiple columns or multiple functions, the cross tabs will outperform the PIVOT function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 9

Jeff Moden does a good job of showing performance comparisons

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

Neil Burton SSC-Insane Points: 22908 More actions · Answer 10

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 used it for the first time the other day. It was the best way of turning columns of day of the week bit flags in to rows. I didn't use an aggregate though. I'm so pleased with something else I did with it I want to share it with the world! 😎

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

call.copse SSCoach Points: 17960 More actions · Answer 11

jshahan (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 like PIVOT because you can use it to do a dynamic number of columns on the back end.

I had tended to use the cross tab approach, recently (well, last 5 or 6 years) switched back to pivots. Mainly I guess because the DBA at our office showed me a decent way to accomplish what I needed and I have followed that ever since. I would generally use this in circumstances where I do have a dynamic column set (meaning use of ADO for programmatic retrieval, dev types) and while both approaches perform similarly in my experience since I properly grokked the PIVOT way it's just seemed slightly more elegant.

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 12

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 use both, but the company I worked for has used it frequently before I came on board.

Personally, I use PIVOT statements more when pivoting data horizontally. It's easier to write for me and the way I format a PIVOT statement is easier to read versus a CASE statement.

But, when it comes to 100 million rows, I stick to CASE for performance.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 13

Luis Cazares (8/25/2014)
jshahan (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 like PIVOT because you can use it to do a dynamic number of columns on the back end.
You can do that with cross tabs as well. 😀

Any good example with Cross tab query vs PIVOT?

Thanks

Hany Helmy SSChampion Points: 13921 More actions · Answer 14

Eirikur Eiriksson (8/24/2014)
Since you wrote such a long post Tom, I guess you really got it wrong 😛

😀

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant