How to transpose from Row to Columns with out using Pivot

Question

How to transpose from Row to Columns with out using Pivot

Viewing 4 posts - 31 through 33 (of 33 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Paul White (6/28/2009)

Jeff, could you let me have the number of your handrail-installation team please? 😀
PIVOT can be used with dynamic SQL, just the same as the CASE...SUM idea.

Heh... it IS an occupational hazard when you do you best on the forums... it's a sign of active participation and that's actually a good thing. The number you seek is 1-800-RIDE-MYA.

I agree... PIVOT can constructed using dynamic SQL.

By the way... when they actually do install the handrails, tell them to use large threads... keeps tear-outs from happening. 😀

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

lmu92 (6/28/2009)
Paul White (6/28/2009)
lmu92 (6/28/2009)
Paul White (6/27/2009)
Clearly, in this case, and with this data distribution, there is nothing to choose between the three methods. ...
Paul
But how about the method I posted above (let's call it "Sub-Cross-Tab")?
That's why I stressed 'in this case, and with this data distribution' 😛
I might have been a little confused about what you where referring to as "this case , and with this data distribution".
I thought you were talking about the scenario Chris described. But after reviewing your posts it becomes clear that you were talking about the test scenario you set up. Sorry for the misinterpretation and the confusion I caused. :Whistling:

Just as a side bar, custom solutions can absolutely cause huge performance gains in certain instances of data. I've been known to use them myself. But as both of you have pointed out, it does rely on the data meeting certain constraints. Unless you can somehow guarantee that the data will always meet such constraints, you might want to stick with the more general solution so that performance problems don't suddenly appear when the constraints change. Most everyone will have forgotten that it was a custom solution when the constraints eventually do change and, of course, any performance problems will surface when you can least afford such problems. Documentation will help, but that probably won't help find the performance problem (although it will help confirm it, once found) in the heat of battle nor will it provide the information necessary to quickly rewrite the code.

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

Paul White SSC Guru Points: 150468 More actions · Answer 3

Jeff Moden (6/28/2009)
The number you seek is 1-800-RIDE-MYA.

I have no icons for how funny that is.

Jeff Moden (6/28/2009)
By the way... when they actually do install the handrails, tell them to use large threads... keeps tear-outs from happening. 😀

:pinch:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 4

Guys, thanks for all the responses on my question, I love to see people digging into the engine compartment of the car instead of just saying it’s a “blue car and it takes me from point A to B”.

Currently I’m sticking with the group option, not that the Pivot option is bad or anything, but just a bit foreign at this stage. I’m from a Teradata background and which didn’t (not that I’m aware of), have the Pivot option.

I must admit that this site is one of the best sites for SQL Server feedback! Thanks again, and I will be posting some more question during this week, as my Warehouse in now coming to a stage where I must start writing queries on the data and some of the queries could be a bit hairy, but I got full confidence that I’ll receive some more of this wonderful answers!