Comma-Separated String into Table's Column in SQL Server

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

In SQL, how to convert a column of a comma separated key string to a comma separated value string

Next time you would need to provide ##1-4.
And learn from this answer what it means, i.e. a minimal reproducible example.
You copy it to SSMS and launch it there.

Here is how to implement it in SQL Server 2016:

• STRING_SPLIT() to break it down, one AssignedTo per row.
• SELECT ... FOR XML ... to revert it back to one row for each task.

SQL

-- DDL and sample data population, start
DECLARE @tblA TABLE (TaskID INT PRIMARY KEY, TaskName VARCHAR(100), AssignedTo VARCHAR(30));
INSERT INTO @tblA (TaskID, TaskName, AssignedTo) VALUES
(1, 'Task 1', '1,4'),
(2, 'Task 2', '3'),
(3, 'Task 3', '2,3'),
(4, 'Task 4', '2,4,5');

DECLARE @tblB TABLE (AssigneeID INT PRIMARY KEY, [Name] VARCHAR(30));
INSERT INTO @tblB (AssigneeID, [Name]) VALUES
(1, 'John Smith'),
(2, 'Janet Wright'),
(3, 'Tom Morgan'),
(4, 'Kevin Warren'),
(5, 'Mike Taylor')
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH cte AS
(
    SELECT * FROM @tblA
        CROSS APPLY (SELECT value FROM STRING_SPLIT(AssignedTo, @separator)) AS x
        INNER JOIN @tblB AS b ON x.value = b.AssigneeID
)
SELECT p.TaskID, p.TaskName
    , STUFF((SELECT DISTINCT
                     CONCAT(@separator, c.Name)
                     FROM cte AS c
                     WHERE c.TaskID = p.TaskID
                     FOR XML PATH ('')),
             1, 1, '') AS NameAssignedTo
FROM cte AS p
GROUP BY p.TaskID, p.TaskName;

Output

+--------+----------+---------------------------------------+
| TaskID | TaskName |            NameAssignedTo             |
+--------+----------+---------------------------------------+
|      1 | Task 1   | John Smith,Kevin Warren               |
|      2 | Task 2   | Tom Morgan                            |
|      3 | Task 3   | Janet Wright,Tom Morgan               |
|      4 | Task 4   | Janet Wright,Kevin Warren,Mike Taylor |
+--------+----------+---------------------------------------+

Insert data into a table from comma separated string (T-SQL)

This can be achieved with a Table Value Constructor

Example:

Select *
From (Values (1),(2),(3),(4)) tabA (ColA)

Comma-separated String into Table's Column in SQL Server

Create a function:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    DECLARE @Index INT
    DECLARE @Slice nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESN’T EQUAL ZERO FIRST TIME IN LOOP
    SELECT @Index = 1
    WHILE @Index !=0
        BEGIN
            SELECT @Index = CHARINDEX(@Delimiter,@String) --Getting the indexof the first Occurrence of the delimiter

            -- Saving everything to the left of the delimiter to the variable SLICE
            IF @Index !=0
                SELECT @Slice = LEFT(@String,@Index - 1)
            ELSE
                SELECT @Slice = @String

            -- Inserting the value of Slice into the Results SET
            INSERT INTO @Results(Items) VALUES(@Slice)

            --Remove the Slice value from Main String
            SELECT @String = RIGHT(@String,LEN(@String) - @Index)

            -- Break if Main String is empty
            IF LEN(@String) = 0 BREAK
        END
    RETURN
END

Pass the string @str and the delimiter (,) to the function.

SELECT Items FROM [dbo].[Split] (@str, ',')

It will return the result as a table:

Items

0.00
0.00
1576.95
0.00
4105.88
1017.87
0.00
6700.70

See SQL Fiddle

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

sql server split comma separated values into columns

Dynamically solve this problem, use DSQL to add more columns in the result accordingly.

--create split function
CREATE FUNCTION [dbo].[SO_Split]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(

    Id int identity(1,1),
    Value nvarchar(100)
)
AS 
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
    Set @List =Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))
    Return
END

--below is the dynamic solution for this problem
declare @sql nvarchar(3000) = 'select *'
declare @cnt int = 1
declare @rowNum int = (select max(a) from (select(select max(id) as id_max from dbo.so_split(mul_query,'*')) as a from #test) as b)

while(@cnt <= @rowNum)
begin
    set @sql = @sql + N', ISNULL((select value from dbo.so_split(mul_query,''*'') where id = '+cast(@cnt as nvarchar(5))+N'),''1'')'
    set @cnt = @cnt + 1
end

set @sql = @sql + N' from #test'

exec sp_executesql @sql

The result is attached below.

How to view a column values in comma separated string with group by clause using SQL query?

You can use the STUFF() as shown below. Just replace cte with your original query. Another way is to create a temp table with your current result and replace that cte with the temp table newly created.

SELECT DoNum
        , CardCode
        , CardName
        , SipCode
        , SlpName
        , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,U_Product_Type) 
         FROM [cte] t1
         WHERE (DoNum = cte.DoNum
         and CardCode = t1.CardCode
         and CardName = t1.CardName
         and SipCode = t1.SipCode
         and SlpName = t1.SlpName) 
         FOR XML PATH ('')), 1, 2, '') AS U_Product_Type
         ,sum(LineTotal) as LineTotal
         ,sum(dwpnt) as dwpnt
FROM cte
group by DoNum
, CardCode
, CardName
, SipCode
, SlpName

Live Demo

---

Related Topics