trim comma delimited values in a single row to multiple rows

Question

trim comma delimited values in a single row to multiple rows

SSC Veteran

Points: 250

April 29, 2007 at 11:41 pm

Hi, I want to know if anyone can help me trim values of comma delimited types into multiple rows. Below is an illustration of what i want to achieve. thanks

CARKEY	CARTYPE	DEFECTKEYS
1	CAR A	1250
2	CAR B	126, 4548, 1250
3	CAR C	6548
4	CAR D	245, 9845, 5664, 3654, 1001


CARKEY	CARTYPE	DEFECTKEYS
1	CAR A	1250
2	CAR B	126
2	CAR B	4548
2	CAR B	1250
3	CAR C	6548
4	CAR D	245
4	CAR D	9845
4	CAR D	5664
4	CAR D	3654
4	CAR D	1001

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Using the function fnParseList here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

INSERT NewTable (CarKey, CarType, DefectKeys)

SELECT t.CarKey, t.CarType, LTRIM(RTRIM(x.Data))

FROM OldTable AS t

CROSS APPLY dbo.fn(',', t.DefectKeys) AS x

N 56°04'39.16"
E 12°55'05.25"