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
karinos57Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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
dqmqCommented:
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

Open in new window


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

Open in new window

0
mimran18Commented:

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]

Open in new window

;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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dqmqCommented:
@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
mimran18Commented:
@dqmq:  Thank you.

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

http://www.sqlservercentral.com/articles/XML/66932/
0
karinos57Author Commented:
thnx for all your help.  this was great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.