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.