Splitting Comma Separated Values in Columns to Multiple Rows in SQL Server

Split comma separated values of a multiple column in row in SQL query

You can use recursive CTEs for this:

with cte as (
      select code, cast(NULL as varchar(max)) as address, cast(NULL as varchar(max)) as phno,
             cast(address + ',' as varchar(max)) as rest_address, cast(phno + ',' as varchar(max)) as rest_phno, 0 as lev
      from t
      union all
      select  code, left(rest_address, charindex(',', rest_address) - 1), left(rest_phno, charindex(',', rest_phno) - 1),
              stuff(rest_address, 1, charindex(',', rest_address), ''), stuff(rest_phno, 1, charindex(',', rest_phno), ''), lev + 1
      from cte
      where rest_address <> ''
     )
select code, address, phno
from cte
where lev > 0;

Here is a db<>fiddle.

If you can have more than 100 elements in the lists, you'll need option (maxrecursion 0).

Split comma separated value in to multiple rows in mysql

If you know the maximum number, you can use a bunch of union alls. For your sample data, this is sufficient:

select col1, substring_index(col2, ',', 1)
from t
union all
select col1, substring(substring_index(col2, ',', 2), ',', -1)
from t
where col2 like '%,%'
union all
select col1, substring(substring_index(col2, ',', 3), ',', -1)
from t
where col2 like '%,%,%'
union all
select col1, substring(substring_index(col2, ',', 4), ',', -1)
from t
where col2 like '%,%,%,%';

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

Splitting Comma separated values in columns to multiple rows in Sql Server

SELECT col1,
       col2,
       Split.a.value('.', 'VARCHAR(100)') col3
FROM   (SELECT col1,
               col2,
               Cast ('<M>' + Replace(col3, ' ', '</M><M>') + '</M>' AS XML) AS Data
        FROM   [table]) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

How to split comma delimited data from one column into multiple rows

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

SELECT t.Employee_FullName,
       Code = LTRIM(j.value),
       Hours = MAX(CASE j.[key] 
                   WHEN 0 THEN RegularTime
                   WHEN 1 THEN DoubleTime
                   WHEN 2 THEN Overtime END)
  FROM dbo.MyTable AS t
  CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
  GROUP BY t.Employee_FullName, LTRIM(j.value);

• Example db<>fiddle

---

Related Topics