Help! Parse data in a column into multiple records. Data is Comma Delimited

Question

Post reply

Help! Parse data in a column into multiple records. Data is Comma Delimited

John-79567

SSCommitted

Points: 1892
More actions
May 30, 2005 at 11:57 am

#67087

I need to parse data within a field into multiple records
Ex: Here is the table layout
Sku                         Description
123                         item 1, item 2, item 3, etc

I want to seperate the item list into individual records like this:
Sku                    Description
123                    item 1
123                    item 2
123                    item 3

Viewing 8 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

CREATE TABLE #showme

(

sku INT

, item VARCHAR(8000)

)

INSERT INTO #showme

SELECT 123, '10001,10002,10003,10004,10005,10006,10007'

UNION ALL

SELECT 124, '20001,20002'

SELECT

sku, CAST(RIGHT(LEFT(item,Number-1)

, CHARINDEX(',',REVERSE(LEFT(','+item,Number-1)))) AS CHAR(30))

FROM

master..spt_values, #showme

WHERE

Type = 'P' AND Number BETWEEN 1 AND LEN(item)+1

AND

(SUBSTRING(item,Number,1) = ',' OR SUBSTRING(item,Number,1) = '')

DROP TABLE #showme

sku

----------- ------------------------------

123 10001

123 10002

123 10003

123 10004

123 10005

123 10006

123 10007

124 20001

124 20002

(9 row(s) affected)

You might also want to have a look at http://www.sommarskog.se/arrays-in-sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

John-79567 SSCommitted Points: 1892 More actions · Answer 2

Frank thanks for the quick reply. I have a couple of questions

1) please explain the first insert into #showme

"select 123, '10001,10002,10003,10004,10005,10006,10007'"

I need to parse over 600 records with varying number of items per sku. Will your statement allow me to do that? Im a tsql beginner, so i apologize in advance if the answer is obvious.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Isn't it strange? I have an evening off, kids are asleep very early and I hang around this forum.

Okay, I've just created a test scenario here. Interesting for you is only that part

SELECT

sku, CAST(RIGHT(LEFT(item,Number-1)

, CHARINDEX(',',REVERSE(LEFT(','+item,Number-1)))) AS CHAR(30))

FROM

master..spt_values, #showme

WHERE

Type = 'P' AND Number BETWEEN 1 AND LEN(item)+1

AND

(SUBSTRING(item,Number,1) = ',' OR SUBSTRING(item,Number,1) = '')

Replace #showme with the name of your table. As you can see in my example, the number of items per sku are unequal. So, the query should catch this.

...and there is no need to apologize for asking!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

John-79567 SSCommitted Points: 1892 More actions · Answer 4

Looks like that does the trick. I appreciate the help especially during a day off.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

May 31, 2005 at 6:48 am

#561860

He's never really off .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I guess you can understand, that online communities can be a drug...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

It surely is addictive... I wouldn't call it a drug thaugh.. doesn't have as many side effects!!!