How to parse two csv columns

brenda grossnickle 186 Reputation points

I have two columns that are in csv format. the first column is product and the second is sub_product. first entry in product is associated with the first entry in sub_product, the second entry in product is associated with the second entry in sub_product. There will always be the same number of entries in product and sub_product.

I want to split out the two csv columns and give each one a seperate row. See below for an example. Thanks.

If object_ID(N'tempdb..#product') is not NULL
    drop table #product
create table #product (vendor int, product varchar(128), sub_product varchar(128))

insert into #product (vendor, product, sub_product)
select * from (values 
(111, 'brushes,brushes,detergent', 'hair,tooth,laundry'),
(222, 'paper,paper', 'legal,note')) as temp (vendor, product, sub_product)

desired result set output

vendor, product, sub_product
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
{count} votes

2 answers

Sort by: Most helpful
  1. LiHong-MSFT 10,031 Reputation points

    Hi,@brenda grossnickle
    If you got SQL Server 2016 (13.x) and later,you can have a try on STRING_SPLIT function.Please check this:

    ;WITH CTE1 AS  
    (SELECT vendor,value AS product ,ROW_NUMBER()OVER(PARTITION BY vendor ORDER BY product) RNum  
     FROM #product CROSS APPLY string_split(product,',')   
    ),CTE2 AS   
    (SELECT vendor,value AS sub_product ,ROW_NUMBER()OVER(PARTITION BY vendor ORDER BY sub_product) RNum  
     FROM #product CROSS APPLY string_split(sub_product,',')   
    SELECT A.vendor,A.product,B.sub_product   
    FROM CTE1 A JOIN CTE2 B ON A.vendor =B.vendor AND A.RNum =B.RNum  


    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Yitzhak Khabinsky 24,011 Reputation points

    Hi @brenda grossnickle ,

    Please try the following solution.

    It is JSON based. It guarantees a correct sequential order.


    -- DDL and sample data population, start  
    DECLARE @tbl table (vendor int, product varchar(128), sub_product varchar(128));  
    INSERT INTO @tbl (vendor, product, sub_product) VALUES  
    (111, 'brushes,brushes,detergent', 'hair,tooth,laundry'),  
    (222, 'paper,paper', 'legal,note');  
    -- DDL and sample data population, end  
    SELECT * FROM @tbl;  
    DECLARE @separator CHAR(1) = ',';  
    WITH rs AS   
         SELECT *  
             , ar1 = '["' + REPLACE(product, @separator, '","') + '"]'  
             , ar2 = '["' + REPLACE(sub_product, @separator, '","') + '"]'  
         FROM @tbl  
     SELECT vendor, product.[value] AS [product], sub_product.[value] AS sub_product  
     FROM rs  
        CROSS APPLY OPENJSON (ar1, N'$') AS product  
        CROSS APPLY OPENJSON (ar2, N'$') AS sub_product  
     WHERE product.[key] = sub_product.[key];  


    | vendor |  product  | sub_product |  
    |    111 | brushes   | hair        |  
    |    111 | brushes   | tooth       |  
    |    111 | detergent | laundry     |  
    |    222 | paper     | legal       |  
    |    222 | paper     | note        |  
    0 comments No comments