How to split one field into many

Question

Post reply

How to split one field into many

msgoldman

Newbie

Points: 1
More actions
May 7, 2009 at 9:08 am

#209194

hello,
i'm trying to split one row into many. i found this succinct explanation from another post:
I have a select statement that returns a number of columns.
ONE of the columns has multiple values in it split by a comma. I need to split this column to repeat all other info.
EG:
X Y Z
NFC EAST Giants,Eagles,Cowboys
I need this to become
NFC EAST Giants
NFC EAST Eagles
NFC EAST Cowboys

Viewing 3 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you have a Numbers/Tally table, this will do that kind of thing:

select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)

from dbo.Numbers

where Number <= len(@String)

and substring(',' + @String, Number, 1) = ','

order by Number;

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

GSquared (5/7/2009)
If you have a Numbers/Tally table, this will do that kind of thing:
select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)
from dbo.Numbers
where Number <= len(@String)
and substring(',' + @String, Number, 1) = ','
order by Number;

If you don't have a Tally or Numbers table and, maybe, don't even know what it is or how it works, please see the article at the following URL.

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

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