convert CSV record in column to multiple rows

Question

select * from responses brings:

2 | user_name | green,red

Can I change the query so that it brings it in the following format?:

2 | user_name |green
2 | user_name |red

number of CSV values in third column varies from record to record.

Answer 1

I am not sure the mysql syntax to use and if this is even possible in mysql but I know it is possible in SQL Server (tsql) as I have done it before (no...I don't have the code anymore).

High level process.

1. Function to split string based on a comma (should probably make it more general). The function should return a table (either variable if possible or write to a temp table somewhere and perhaps return the name of the temp table).

2. The temp table will be 1 row per CSV item

3. Then you can join your data set with this temp table to get what you want.

4. This will have to be written in a stored procedure I would imagine. I bet ther is a way to do it in a recursive CTE as well.

Code for my TSQL split function is below. You see I pass in a join_value variable which could be your username perhaps.

ALTER FUNCTION [common].[split_string](@String varchar(8000), @Delimiter char(1), @join_value bigint)     
returns @temptable TABLE (items varchar(8000), join_value bigint)     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     

    select @idx = 1     
        if len(@String)<1 or @String is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     

        if(len(@slice)>0)
            insert into @temptable(items, join_value) values(rtrim(ltrim(@slice)), @join_value)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end