• 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:
karinos57
3 Solutions
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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