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
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.
Have your issue resolved?If not , please feel free to share with us with more details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!