Comma Separated Values in One Column - 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

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).

Multiple rows to one comma-separated value in sql

Use where clause :

select name , STUFF((SELECT '; ' + facilty 
                     FROM leads  
                     FOR XML PATH('')
                    ),1,2,'') as facilty, address 
from leads
where name is not null;

Concatenating Column Values into a Comma-Separated List

You can do a shortcut using coalesce to concatenate a series of strings from a record in a table, for example.

declare @aa varchar (200)
set @aa = ''

select @aa = 
    case when @aa = ''
    then CarName
    else @aa + coalesce(',' + CarName, '')
    end
  from Cars

print @aa

How to get column values in one comma separated value

You tagged the question with both sql-server and plsql so I will provide answers for both SQL Server and Oracle.

In SQL Server you can use FOR XML PATH to concatenate multiple rows together:

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

See SQL Fiddle with Demo.

In Oracle 11g+ you can use LISTAGG:

select "User",
  listagg(department, ',') within group (order by "User") as departments
from yourtable
group by "User"

See SQL Fiddle with Demo

Prior to Oracle 11g, you could use the wm_concat function:

select "User",
  wm_concat(department) departments
from yourtable
group by "User"

Comma separated values of multiple columns in single column in sql server

..from sqlserver 2017 ..fiddle

select *
from
(
values
(1, '204,201,33', 'Invalid Object,Out Of Range,Invalid Format'),
(2, '21,44', 'FileInvalid,Invalid date'),
(3, '20', 'Invalid parse')
) as t(Id, Errorcode, ErrorDescription)
cross apply
(
select string_agg(c.value+'-'+e.value, ', ') within group (order by cast(c.[key] as int)) as error
from openjson('["'+replace(string_escape(t.Errorcode, 'json'), ',', '","')+'"]') as c
join openjson('["'+replace(string_escape(t.ErrorDescription, 'json'), ',', '","')+'"]') as e on c.[key] = e.[key]
) as e;

..sqlserver 2016..fiddle

select *
from
(
values
(1, '204,201,33', 'Invalid Object,Out Of Range,Invalid Format'),
(2, '21,44', 'FileInvalid,Invalid date'),
(3, '20', 'Invalid parse')
) as t(Id, Errorcode, ErrorDescription)
cross apply
(
select stuff ((
   select ', '+c.value+'-'+e.value
   from openjson('["'+replace(string_escape(t.Errorcode, 'json'), ',', '","')+'"]') as c
   join openjson('["'+replace(string_escape(t.ErrorDescription, 'json'), ',', '","')+'"]') as e on c.[key] = e.[key]
   order by cast(c.[key] as int)
   for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as error
) as e;

Multiple rows to one comma-separated value in Sql Server

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);

SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;