How to parse two csv columns

Question

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
'111','brushes','hair'
'111','brushes','tooth'
'111','detergent','laundry'
'222','paper','legal'
'222','paper','note'

Answer 1

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  

Output:

Best regards,
LiHong

---

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.

Answer 2

Hi @brenda grossnickle ,

Please try the following solution.

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

SQL

-- 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];  

Output

+--------+-----------+-------------+  
| vendor |  product  | sub_product |  
+--------+-----------+-------------+  
|    111 | brushes   | hair        |  
|    111 | brushes   | tooth       |  
|    111 | detergent | laundry     |  
|    222 | paper     | legal       |  
|    222 | paper     | note        |  
+--------+-----------+-------------+