SSIS C# Transform or SQL for separating multiple delimited records into separate rows

Question

I have a table with some columns having multiple values inside separated by comma. I want to split them as new rows. I can obtain correct result if there is only one column involved. But there are several columns with comma separated values to be split all at once. The problem is that the record is multiplying to unnecessary records when I use either

OUTER APPLY dbo.split([columnName], ',')

or

cross apply STRING_SPLIT ([columnName], ',')

in SQL. The same result I am getting when using SSIS. Can someone help me obtain the expected result using SQL or SSIS?

Sample record and expected result:

Note that there are records with values higher than two entries, so comma will be encountered many times.

Current record:

ID  Fruit Name    Color        Taste    Price
1   Apple       red,green   sweet,sour  20,30

Expected result after splitting the comma-delimited values into rows:

ID  Fruit Name      Color   Taste   Price
1   Apple           red     sweet   20
1   Apple           green   sour    30

What I am getting is:

ID  Fruit Name   Color  Taste   Price
1   Apple        red    sweet   20
1   Apple        red    sour    30
1   Apple        green  sweet   20
1   Apple        green  sour    30

a full code sample would be more helpful than just a little snippet. P.S. SSIS is just a way to run SQL automatically, so I'm unclear why you are making a distinction about that tool? — ADyson, Nov 29 '17 at 11:51
Post your entire query. Does your split function return an id column? — Tab Alleman, Nov 29 '17 at 13:50
Here is the sample code that I am using that's showing wrong result as it is matching each and every item causing the records to multiply unnecessarily. SELECT ID1 --(ID1 and ID2 are key combinations to determine unique records) ,ID2 ,a.item AS DelimitedColumn1 ,b.item AS DelimitedColumn2 ,c.item AS DelimitedColumn3 FROM [TABLE] t1 outer apply dbo.split(t1.DelimitedColumn1, ',') a outer apply dbo.split(t1.DelimitedColumn2, ',') b outer apply dbo.split(t1.DelimitedColumn3, ',') c — SQL Knows, Nov 30 '17 at 04:20

score 0 · Answer 1 · answered Nov 29 '17 at 13:52

I know its a sloppy solution but it might work for you until you find a more optimized Query.

DECLARE @MyTableVar table(  
    id int,
    fName NVARCHAR(400),  
    Color NVARCHAR(400),  
    Taste NVARCHAR(400),  
    Price NVARCHAR(400));  

    DECLARE @temp table(  
    id int,
    Color NVARCHAR(400),  
    Taste NVARCHAR(400),  
    Price NVARCHAR(400));  

    INSERT INTO @MyTableVar VALUES(1,'Apple','red,green,blue,green','sweet,sour,salty,funny','20,30,10,60')

    INSERT INTO @temp (id,Color) SELECT  mTable.id as ID,  c.value as Price  from @MyTableVar as mTable OUTER  apply STRING_SPLIT (Color, ',')as c
    INSERT INTO @temp (id,Taste) SELECT  mTable.id as ID,  t.value as Price  from @MyTableVar as mTable OUTER  apply STRING_SPLIT (Taste, ',')as t
    INSERT INTO @temp (id,Price) SELECT  mTable.id as ID,  p.value as Price  from @MyTableVar as mTable OUTER  apply STRING_SPLIT (Price, ',')as p

    DECLARE @outputTable table(  
    id int,
    fName NVARCHAR(400), 
    Color NVARCHAR(400),  
    Taste NVARCHAR(400),  
    Price NVARCHAR(400)); 


    INSERT INTO @outputTable (id,fName,Color,Taste,Price) 
        SELECT t4.id,t4.fName, t1.Color, t2.Taste,t3.Price
FROM (
   SELECT Color,
          ROW_NUMBER() OVER (ORDER BY id) AS rn
   FROM @temp WHERE Color IS NOT NULL) AS t1
FULL OUTER JOIN  (
   SELECT Taste,
          ROW_NUMBER() OVER (ORDER BY id) AS rn
   FROM @temp WHERE Taste IS NOT NULL) AS t2
ON t1.rn = t2.rn
FULL OUTER JOIN  (
   SELECT Price,
          ROW_NUMBER() OVER (ORDER BY id) AS rn
   FROM @temp WHERE Price IS NOT NULL) AS t3
ON  t1.rn = t3.rn
FULL OUTER JOIN  (
   SELECT id,fName
   FROM @MyTableVar) AS T4
ON  t1.rn = t3.rn


Select * from @outputTable;

Modify is as you see fit to work for your needs, I have run some tests with extra ',' in the values it seems to works fine.

The basic idea is to create a temporary table store the values there as we need them and then display (and store) the view we want into an output table (which you can replace with whatever you like).

I hope this works for you, for any questions feel free to ask me!

Hi, thanks for this. basically your approach is the same as Rajneesh Vaishwar answer. Still I am having issues... — SQL Knows, Nov 30 '17 at 04:16

score 0 · Answer 2 · answered Nov 29 '17 at 14:28

0

You can use below query which will give the output as you are expecting!

DROP TABLE if EXISTS #temp
Select 
    1 as ID ,
    'Apple'  AS FruitName , 
    'red,green' as Color ,
    'sweet,sour' as Taste, 
    '20,30' AS Price 
into #temp

;WIth Color as ( 
select id,FruitName,value,ROW_NUMBER() over (order by id) RN 
                   from #temp cross apply string_split(Color,',')),
Taste as (
select id,FruitName,value,ROW_NUMBER() over (order by id) RN 
                   from #temp cross apply string_split(Taste,',')),
Price as (
select id,FruitName,value,ROW_NUMBER() over (order by id) RN 
                   from #temp cross apply string_split(Price,','))

Select c.ID ,
       C.FruitName,
       c.value as Color,
       t.Value as Taste,
       p.value as Price
    from Color c
        INNER JOIN Taste t on c.ID=t.id and c.rn=t.RN
        INNER JOIN Price p on c.id=p.id and c.RN=p.rn

answered Nov 29 '17 at 14:28

Raj

462
3
15

Hi, Thanks for this. But this seems to work only for scnearios that the columns have the same number of delimited values. What if the Color has only green and red and then the Taste has sweet,sour,salty,funny.? – SQL Knows Nov 30 '17 at 04:15
Here is the sample code that I am using that's showing wrong result as it is matching each and every item causing the records to multiply unnecessarily. SELECT ID1 --(ID1 and ID2 are key combinations to determine unique records) ,ID2 ,a.item AS DelimitedColumn1 ,b.item AS DelimitedColumn2 ,c.item AS DelimitedColumn3 FROM [TABLE] t1 outer apply dbo.split(t1.DelimitedColumn1, ',') a outer apply dbo.split(t1.DelimitedColumn2, ',') b outer apply dbo.split(t1.DelimitedColumn3, ',') c – SQL Knows Nov 30 '17 at 04:15
ok you can use Full outer join and use isnull instead of inner join. I think this should give you an idea for further. Also its good to give sample data with all scenarios covered. – Raj Nov 30 '17 at 05:00

SSIS C# Transform or SQL for separating multiple delimited records into separate rows

2 Answers2

Linked