• Status: Solved
• Priority: Medium
• Security: Public
• Views: 305
• Last Modified:

# insert rows based on words in a field seperated by comma

Hi,
i have been struggling to find a way to separate category names in a field which are separated by comma and would like to insert a new row for each category name.    Can someone help me solve this puzzle?  i have sql server 2008.
Here is my current table:

Current Issue:
ID             Product               Category
1                CAM                   X1, X2, X3
2                 ELEC                  Y1, Y2, Y3

DESIRED RESULTS
D             Product               Category
1                CAM                   X1
1                CAM                   X2
1                CAM                   X3
2                 ELEC                  Y1
2                 ELEC                  Y2
2                 ELEC                  Y3

thanks for your time
0
karinos57
Asked:
3 Solutions

Commented:
how long is category?

this deals with categories up to 9998 in length...

;with n1 as (select 0 as n union all select 1 union all select 2
union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8
union all select 9)
,n2 as (select (a.n*10)+b.n as n from n1 as a,n1 as b) --100 rows
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b) --1000 rows
,n4 as (select (a.n*10)+b.n as n from n3 as a,n1 as b) --10000 rows
,pharses as
(select id,product,','+category+',' as phrase
from yourtable
Where category like '%,%')
,parsed as
(select id,product,phrase,n
,row_number() over (partition by id,product
order by n) as rn
from phrases
cross join n4
where substring(phrase,n,1)=','
and len(phrase)<=n
)
,cte as
(select id,product,substring(phrase,a.n+1,b.n-a.n-1) as category
from parsed as a
inner join parsed as b
on a.id=b.id
and a.product=b.product
and a.rn=b.rn-1
)
Insert into Yourtable2 (id,product,category)
select id,product,category
from cte as a
where not exists (select id from yourtable2 as x
where a.id=x.id
and a.product=x.product
ans a.category=x.category)

0

Commented:
First create a table-valued UDF like this:

``````CREATE FUNCTION [dbo].[Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1

DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))

SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END

INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))

RETURN
END
``````

It's important to note that you pass that function a delimited string plus the delimiter and it returns a table with columns ID (identity) and Data.

Then use that function to expand your delimited string, like this:

``````Select T.id, T.Product, F.Data
from YourTable T
cross apply dbo.split(t.Category,',')
``````
0

Commented:

``````Select 1  as [ID]  ,            'CAM'   as [Product]         ,       'X1, X2, X3'  as [Category] into [Test] Union ALL
Select 2   as [ID]    ,           'ELEC' as [Product]          ,        'Y1, Y2, Y3' as [Category]
``````
``````;WITH Cte AS
(
SELECT
id, Product  ,
CAST('<M>' + REPLACE( [Category], ',' , '</M><M>') + '</M>' AS XML) AS [Category]
FROM  [Test]
)
,Cte2 as (
Select
ID,Product  ,
Split.a.value('.', 'VARCHAR(100)') AS [Category]
FROM Cte
CROSS APPLY [Category] .nodes('/M') Split(a))

Select * from cte2
``````
0

Commented:
@mimran18>  That's brilliant.  I've simplified it to a single CTE.  Now, I just need to figure out what "split.a.value" is all about--never seen anything like it.  :>)

;WITH Cte AS
(
SELECT
id, Product  ,
CAST('<M>' + REPLACE( [Category], ',' , '</M><M>') + '</M>' AS XML) AS [Category]
FROM  test
)
Select
ID,Product  ,
Split.a.value('.', 'VARCHAR(100)') AS [Category]
FROM Cte
CROSS APPLY [Category] .nodes('/M') Split(a)
0

Commented:
@dqmq:  Thank you.

I used XQuery to split it.
you can review this query details here.

http://www.sqlservercentral.com/articles/XML/66932/
0

Author Commented:
thnx for all your help.  this was great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.