create a function to make columns into rows

Question

Post reply

create a function to make columns into rows

Viewing 6 posts - 16 through 20 (of 20 total)

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

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

Jason Selburg (7/7/2008)
Sure, create a Tally table and use the below function ....
(compliments to Jeff Moden)

Thanks for the honorable mention, Jason. 🙂

--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: 1004376 More actions · Answer 2

DBA (7/8/2008)
thank you!, both functions would do the same as using unpivot?

No... unpivot needs a predefined number of columns... neither the Tally table method nor the XML method need to know how many columns there are. In fact, both the Tally table method and the XML method can split a whole column of delimited data without knowing how many elements there are in any row and you don't need a function for either.

For more information on the Tally table method, please see the following two articles...

http://www.sqlservercentral.com/articles/TSQL/62867/

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

Yes, you can escape/replace the XML characters. But again, you're adding more code to accomplish the same thing. It just comes down to, do you want to have a Numbers table or not? Is disk space more expensive than code performance?

The XML version does have an advantage of being able to deal well with multi-character delimiters, that I forgot to mention. So, if you're dealing with those, or might, use the XML version.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

Heh... space used... the Tally or "Numbers" table only takes 136KB for an 11,000 row table and that includes the clustered index in the form of a primary key. For what it does, even in 2k5, I'm not real sure why folks continue to argue about how much room it takes... I've got templates for WORD that take more room. 😛

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Jeff Moden (7/30/2008)
Heh... space used... the Tally or "Numbers" table only takes 136KB for an 11,000 row table and that includes the clustered index in the form of a primary key. For what it does, even in 2k5, I'm not real sure why folks continue to argue about how much room it takes... I've got templates for WORD that take more room. 😛

hmm... I just inherited a bunch of stored procs...each larger than that.

And it seems that the previous owner was worried about such space usage, since there isn't one single comment throughout the code....:P

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 6

Matt Miller (7/30/2008)
Jeff Moden (7/30/2008)
Heh... space used... the Tally or "Numbers" table only takes 136KB for an 11,000 row table and that includes the clustered index in the form of a primary key. For what it does, even in 2k5, I'm not real sure why folks continue to argue about how much room it takes... I've got templates for WORD that take more room. 😛
hmm... I just inherited a bunch of stored procs...each larger than that.
And it seems that the previous owner was worried about such space usage, since there isn't one single comment throughout the code....:P

We must be leading our lives in parallel... I just started a new job on Monday and the use of comments is nearly as non-existent. I have to admit, I did find a proc with two 3 word comments in it today. Woo-hoo! 😛

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