tSQL UNPIVOT of comma concatenated column into multiple rows

Question

I have a table that has a value column. The value could be one value or it could be multiple values separated with a comma:

id | assess_id | question_key | item_value
---+-----------+--------------+-----------
 1 |       859 |     Cust_A_1 |        1,5
 2 |       859 |     Cust_B_1 |          2

I need to unpivot the data based on the item_value to look like this:

id | assess_id | question_key | item_value
---+-----------+--------------+-----------
 1 |       859 |     Cust_A_1 |          1
 1 |       859 |     Cust_A_1 |          5
 2 |       859 |     Cust_B_1 |          2

How does one do that in tSQL on SQL Server 2012?

Check this link - http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — TMNT2014, Aug 11 '14 at 19:10

score 1 · Answer 1 · answered Aug 13 '14 at 19:14

We have a user defined function that we use for stuff like this that we called "split_delimiter":

CREATE FUNCTION [dbo].[split_delimiter](@delimited_string VARCHAR(8000), @delimiter_type CHAR(1))
RETURNS TABLE AS
RETURN
WITH cte10(num) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                         
,cte100(num) AS 
(
    SELECT  1 
    FROM    cte10 t1, cte10 t2
)
,cte10000(num) AS 
(
    SELECT  1 
    FROM    cte100 t1, cte100 t2
)
,cte1(num) AS 
(
    SELECT  TOP (ISNULL(DATALENGTH(@delimited_string),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    FROM    cte10000
)
,cte2(num) AS 
(
    SELECT  1 
    UNION ALL
    SELECT  t.num+1 
    FROM    cte1 t
    WHERE   SUBSTRING(@delimited_string,t.num,1) = @delimiter_type
)
,cte3(num,[len]) AS
(
    SELECT  t.num
            ,ISNULL(NULLIF(CHARINDEX(@delimiter_type,@delimited_string,t.num),0)-t.num,8000)
    FROM    cte2 t
)

SELECT  delimited_item_num    = ROW_NUMBER() OVER(ORDER BY t.num)
        ,delimited_value    = SUBSTRING(@delimited_string, t.num, t.[len])
FROM    cte3 t;
GO

It will take a varchar value up to 8000 characters and will return a table with the delimited elements broken into rows. In your example, you'll want to use an outer apply to turn those delimited values into separate rows:

SELECT  my_table.id, my_table.assess_id, question_key, my_table.delimited_items.item_value
FROM    my_table
OUTER APPLY(
    SELECT  delimited_value AS item_value
    FROM    my_database.dbo.split_delimiter(my_table.item_value, ',')
    ) AS delimited_items

tSQL UNPIVOT of comma concatenated column into multiple rows

1 Answers1