Rows to Columns

Question

Rows to Columns

Viewing 4 posts - 16 through 18 (of 18 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Todd,

There is no doubt that using PIVOT is beneficial in some situations; however, multiple aggregations is not one of them. In your example, you cannot "easily" provide a sum of dollars, count of units and a total of all products sold for a particular TestOrder. In most cases, users want to quantify how much money, how many units, and when items are sold. It would be much more beneficial if PIVOT were able to accommodate more than one aggregation.

Maybe I am reading too much into what I believe the functionality should be, but I have found very few situations where using PIVOT has been more beneficial than standard TSQL.

My blog: http://jahaines.blogspot.com

Todd Townley Ten Centuries Points: 1320 More actions · Answer 2

I will confess that I was not considering multiple aggregations.

When I review the original posting, however, I notice two things:

1.The original post says nothing about aggregating anything. The poster only requests a method to turn rows to columns. Granted, many times aggregation is assumed from there, but that’s not always needed.

2.The original post also says, "Note: Using SQL SERVER 2000”, in which case PIVOT is out of the question anyway. 🙂

Regardless, I think this thread has had some very interesting and very good discussion.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

You are absolutely right this conversation has deviated from the original post. I went off tangent, in my rant about PIVOT 😀

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 4

... and PLEASE DON'T DOUBLE POST!!!!

http://www.sqlservercentral.com/Forums/Topic436570-338-1.aspx

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)