Consider a database table holding names, with three rows:
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
This question is related to
sql
sql-server
csv
string-concatenation
group-concat
I usually use select like this to concatenate strings in SQL Server:
with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N', ' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc
How about this:
ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'
Where the "300" could be any width taking into account the max number of items you think will show up.
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
you can use the FOR JSON syntax
i.e.
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
And the result will become
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
This will work even your data contains invalid XML characters
the '"},{"_":"' is safe because if you data contain '"},{"_":"', it will be escaped to "},{\"_\":\"
You can replace ', ' with any string separator
You can use the new STRING_AGG function
If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one.
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
Postgres arrays are awesome. Example:
Create some test data:
postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
name
-------
Peter
Paul
Mary
(3 rows)
Aggregate them in an array:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Convert the array to a comma delimited string:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
DONE
Since PostgreSQL 9.0 it is even easier.
To avoid null values you can use CONCAT()
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
in Postgres - array_agg
SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;
OR STRING_AGG
SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Here's a sample:
DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
Use COALESCE - Learn more from here
For an example:
102
103
104
Then write below code in sql server,
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
Output would be:
102,103,104
I really liked elegancy of Dana's answer. Just wanted to make it complete.
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
We can use RECUSRSIVITY, WITH CTE, union ALL as follows
declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')
declare @myresult as table(id int,str nvarchar(max),ind int, R# int)
;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte
select top 1 str from @myresult order by R# desc
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Here's a sample:
DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".
on top of Chris Shaffer answer
if your data may get repeated Such as
Tom
Ali
John
Ali
Tom
Mike
Instead of having Tom,Ali,John,Ali,Tom,Mike
You can use DISTINCT to avoid duplicates and get Tom,Ali,John,Mike
DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
One method not yet shown via the XML data() command in MS SQL Server is:
Assume table called NameList with one column called FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
returns:
"Peter, Paul, Mary, "
Only the extra comma must be dealt with.
Edit: As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn't even have to set my variable (defaults to NULL anyhow) and it assumes all entries in my source data table are non blank:
DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData
I am sure COALESCE internally uses the same idea. Lets hope MS don't change this on me.
There are couple ways in oracle,
create table name
(first_name varchar2(30));
insert into name values ('Peter');
insert into name values ('Paul');
insert into name values ('Mary');
Solution is 1:
select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
o/p=> Peter,Paul,Mary
Soution is 2:
select rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
o/p=> Peter,Paul,Mary
Table definition
CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;
Let's insert values into this table
INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');
Procedure starts from here
DECLARE
MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);
BEGIN
SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;
LOOP
SELECT NAME INTO C_NAME FROM
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;
NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;
END LOOP;
dbms_output.put_line(SUBSTR(NSTR,2));
END;
Result
PETER,PAUL,MARY
Use COALESCE - Learn more from here
For an example:
102
103
104
Then write below code in sql server,
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
Output would be:
102,103,104
This worked for me (SqlServer 2016):
SELECT CarNamesString = STUFF((
SELECT ',' + [Name]
FROM tbl_cars
FOR XML PATH('')
), 1, 1, '')
Here is the source : https://www.mytecbits.com/
And a solution for MySql (since this page show up in Google for MySql)
SELECT [Name],
GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',')
FROM tbl_cars
From MySql Documentations
We can use RECUSRSIVITY, WITH CTE, union ALL as follows
declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')
declare @myresult as table(id int,str nvarchar(max),ind int, R# int)
;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte
select top 1 str from @myresult order by R# desc
With TABLE type it is extremely easy. Let's imagine that your table is called Students and it has column name.
declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''
DECLARE @MyTable TABLE
(
Id int identity,
Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)
while @i < @rowsCount
begin
set @names = @names + ', ' + (select name from @MyTable where Id = @i)
set @i = @i + 1
end
select @names
This example is tested in MS SQL Server 2008 R2
Postgres arrays are awesome. Example:
Create some test data:
postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
name
-------
Peter
Paul
Mary
(3 rows)
Aggregate them in an array:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Convert the array to a comma delimited string:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
DONE
Since PostgreSQL 9.0 it is even easier.
How about this:
ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'
Where the "300" could be any width taking into account the max number of items you think will show up.
In SQL Server 2005 and later, use the query below to concatenate the rows.
DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t ) t
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
you can use the FOR JSON syntax
i.e.
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
And the result will become
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
This will work even your data contains invalid XML characters
the '"},{"_":"' is safe because if you data contain '"},{"_":"', it will be escaped to "},{\"_\":\"
You can replace ', ' with any string separator
You can use the new STRING_AGG function
Table definition
CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;
Let's insert values into this table
INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');
Procedure starts from here
DECLARE
MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);
BEGIN
SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;
LOOP
SELECT NAME INTO C_NAME FROM
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;
NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;
END LOOP;
dbms_output.put_line(SUBSTR(NSTR,2));
END;
Result
PETER,PAUL,MARY
Using XML helped me in getting rows separated with commas. For the extra comma we can use the replace function of SQL Server. Instead of adding a comma, use of the AS 'data()' will concatenate the rows with spaces, which later can be replaced with commas as the syntax written below.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
With TABLE type it is extremely easy. Let's imagine that your table is called Students and it has column name.
declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''
DECLARE @MyTable TABLE
(
Id int identity,
Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)
while @i < @rowsCount
begin
set @names = @names + ', ' + (select name from @MyTable where Id = @i)
set @i = @i + 1
end
select @names
This example is tested in MS SQL Server 2008 R2
SELECT PageContent = Stuff(
( SELECT PageContent
FROM dbo.InfoGuide
WHERE CategoryId = @CategoryId
AND SubCategoryId = @SubCategoryId
for xml path(''), type
).value('.[1]','nvarchar(max)'),
1, 1, '')
FROM dbo.InfoGuide info
This can be useful too
create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test
returns
Peter,Paul,Mary
Here is the complete solution to achieve this:
-- Table Creation
CREATE TABLE Tbl
( CustomerCode VARCHAR(50)
, CustomerName VARCHAR(50)
, Type VARCHAR(50)
,Items VARCHAR(50)
)
insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'
-- function creation
GO
CREATE FUNCTION [dbo].[fn_GetItemsByType]
(
@CustomerCode VARCHAR(50)
,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE ( Items VARCHAR(5000) )
AS
BEGIN
INSERT INTO @ItemType(Items)
SELECT STUFF((SELECT distinct ',' + [Items]
FROM Tbl
WHERE CustomerCode = @CustomerCode
AND Type=@Type
FOR XML PATH(''))
,1,1,'') as Items
RETURN
END
GO
-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type)
from Tbl
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
from
(
select
distinct CustomerCode
,CustomerName
,Type
,F.Items
FROM Tbl T
CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
) x
pivot
(
max(Items)
for Type in (' + @cols + ')
) p '
execute(@query)
on top of Chris Shaffer answer
if your data may get repeated Such as
Tom
Ali
John
Ali
Tom
Mike
Instead of having Tom,Ali,John,Ali,Tom,Mike
You can use DISTINCT to avoid duplicates and get Tom,Ali,John,Mike
DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
Depends on your database vendor. MySQL has concat_ws. MS SQL Server expects you to do it in your client application.
Update: you could also do it in an external procedure or UDF, perhaps by using a cursor or calling out to CLR code.
SELECT PageContent = Stuff(
( SELECT PageContent
FROM dbo.InfoGuide
WHERE CategoryId = @CategoryId
AND SubCategoryId = @SubCategoryId
for xml path(''), type
).value('.[1]','nvarchar(max)'),
1, 1, '')
FROM dbo.InfoGuide info
declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone
A recursive CTE solution was suggested, but no code was provided. The code below is an example of a recursive CTE. Note that although the results match the question, the data doesn't quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. Changing it to match all rows in the table is left as an exercise for the reader.
;WITH basetable AS (
SELECT
id,
CAST(name AS VARCHAR(MAX)) name,
ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
COUNT(*) OVER (Partition BY id) recs
FROM (VALUES
(1, 'Johnny', 1),
(1, 'M', 2),
(2, 'Bill', 1),
(2, 'S.', 4),
(2, 'Preston', 5),
(2, 'Esq.', 6),
(3, 'Ted', 1),
(3, 'Theodore', 2),
(3, 'Logan', 3),
(4, 'Peter', 1),
(4, 'Paul', 2),
(4, 'Mary', 3)
) g (id, name, seq)
),
rCTE AS (
SELECT recs, id, name, rw
FROM basetable
WHERE rw = 1
UNION ALL
SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
FROM basetable b
INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
In SQL Server vNext this will be built in with the STRING_AGG function, read more about it here: https://msdn.microsoft.com/en-us/library/mt790580.aspx
MySQL complete Example:
We have Users which can have many Data's and we want to have an output, where we can see all users Datas in a list:
Result:
___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|
Table Setup:
CREATE TABLE `Data` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);
CREATE TABLE `User` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `User` (`id`) VALUES
(0),
(1);
Query:
SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
--SQL Server 2005+
CREATE TABLE dbo.Students
(
StudentId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);
CREATE TABLE dbo.Subjects
(
SubjectId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);
CREATE TABLE dbo.Schedules
(
StudentId INT
, SubjectId INT
, CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
, CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
, CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);
INSERT dbo.Students (StudentId, Name) VALUES
(1, 'Mary')
, (2, 'John')
, (3, 'Sam')
, (4, 'Alaina')
, (5, 'Edward')
;
INSERT dbo.Subjects (SubjectId, Name) VALUES
(1, 'Physics')
, (2, 'Geography')
, (3, 'French')
, (4, 'Gymnastics')
;
INSERT dbo.Schedules (StudentId, SubjectId) VALUES
(1, 1) --Mary, Physics
, (2, 1) --John, Physics
, (3, 1) --Sam, Physics
, (4, 2) --Alaina, Geography
, (5, 2) --Edward, Geography
;
SELECT
sub.SubjectId
, sub.Name AS [SubjectName]
, ISNULL( x.Students, '') AS Students
FROM
dbo.Subjects sub
OUTER APPLY
(
SELECT
CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
+ stu.Name
FROM
dbo.Students stu
INNER JOIN dbo.Schedules sch
ON stu.StudentId = sch.StudentId
WHERE
sch.SubjectId = sub.SubjectId
ORDER BY
stu.Name
FOR XML PATH('')
) x (Students)
;
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
Without grouping
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
With grouping :
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With grouping and sub-sorting
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With a recursive query you can do it:
-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;
-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');
-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank where rang=1
union all
select f0.*, cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1
)
select AllName from tmpRecursive
where rang=NbRow
Using XML helped me in getting rows separated with commas. For the extra comma we can use the replace function of SQL Server. Instead of adding a comma, use of the AS 'data()' will concatenate the rows with spaces, which later can be replaced with commas as the syntax written below.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn't even have to set my variable (defaults to NULL anyhow) and it assumes all entries in my source data table are non blank:
DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData
I am sure COALESCE internally uses the same idea. Lets hope MS don't change this on me.
For Oracle DBs, see this question: How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
The best answer appears to be by @Emmanuel, using the built-in LISTAGG() function, available in Oracle 11g Release 2 and later.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
as @user762952 pointed out, and according to Oracle's documentation http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php, the WM_CONCAT() function is also an option. It seems stable, but Oracle explicitly recommends against using it for any application SQL, so use at your own risk.
Other than that, you will have to write your own function; the Oracle document above has a guide on how to do that.
First of all you should declare a table variable and fill it with your table data and after that, with a WHILE loop, select row one by one and add its value to a nvarchar(max) variable.
Go
declare @temp table(
title nvarchar(50)
)
insert into @temp(title)
select p.Title from dbo.person p
--
declare @mainString nvarchar(max)
set @mainString = '';
--
while ((select count(*) from @temp) != 0)
begin
declare @itemTitle nvarchar(50)
set @itemTitle = (select top(1) t.Title from @temp t)
if @mainString = ''
begin
set @mainString = @itemTitle
end
else
begin
set @mainString = concat(@mainString,',',@itemTitle)
end
delete top(1) from @temp
end
print @mainString
I usually use select like this to concatenate strings in SQL Server:
with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N', ' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc
This method applies to Teradata Aster database only as it utilizes its NPATH function.
Again, we have table Students
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Then with NPATH it is just single SELECT:
SELECT * FROM npath(
ON Students
PARTITION BY SubjectID
ORDER BY StudentName
MODE(nonoverlapping)
PATTERN('A*')
SYMBOLS(
'true' as A
)
RESULT(
FIRST(SubjectID of A) as SubjectID,
ACCUMULATE(StudentName of A) as StudentName
)
);
Result:
SubjectID StudentName
---------- -------------
1 [John, Mary, Sam]
2 [Alaina, Edward]
In Oracle, it is wm_concat. I believe this function is available in the 10g release and higher.
This method applies to Teradata Aster database only as it utilizes its NPATH function.
Again, we have table Students
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Then with NPATH it is just single SELECT:
SELECT * FROM npath(
ON Students
PARTITION BY SubjectID
ORDER BY StudentName
MODE(nonoverlapping)
PATTERN('A*')
SYMBOLS(
'true' as A
)
RESULT(
FIRST(SubjectID of A) as SubjectID,
ACCUMULATE(StudentName of A) as StudentName
)
);
Result:
SubjectID StudentName
---------- -------------
1 [John, Mary, Sam]
2 [Alaina, Edward]
MySQL complete Example:
We have Users which can have many Data's and we want to have an output, where we can see all users Datas in a list:
Result:
___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|
Table Setup:
CREATE TABLE `Data` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);
CREATE TABLE `User` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `User` (`id`) VALUES
(0),
(1);
Query:
SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
A recursive CTE solution was suggested, but no code was provided. The code below is an example of a recursive CTE. Note that although the results match the question, the data doesn't quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. Changing it to match all rows in the table is left as an exercise for the reader.
;WITH basetable AS (
SELECT
id,
CAST(name AS VARCHAR(MAX)) name,
ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
COUNT(*) OVER (Partition BY id) recs
FROM (VALUES
(1, 'Johnny', 1),
(1, 'M', 2),
(2, 'Bill', 1),
(2, 'S.', 4),
(2, 'Preston', 5),
(2, 'Esq.', 6),
(3, 'Ted', 1),
(3, 'Theodore', 2),
(3, 'Logan', 3),
(4, 'Peter', 1),
(4, 'Paul', 2),
(4, 'Mary', 3)
) g (id, name, seq)
),
rCTE AS (
SELECT recs, id, name, rw
FROM basetable
WHERE rw = 1
UNION ALL
SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
FROM basetable b
INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.
Below is an example that uses SQL Server "Information_Schema.Columns" table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.
SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
Without grouping
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
With grouping :
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With grouping and sub-sorting
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Depends on your database vendor. MySQL has concat_ws. MS SQL Server expects you to do it in your client application.
Update: you could also do it in an external procedure or UDF, perhaps by using a cursor or calling out to CLR code.
One method not yet shown via the XML data() command in MS SQL Server is:
Assume table called NameList with one column called FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
returns:
"Peter, Paul, Mary, "
Only the extra comma must be dealt with.
Edit: As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
@Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
@User1460901 You can try something like this:
WITH cte_base AS (
SELECT CustomerCode, CustomerName,
CASE WHEN Typez = 'Breakfast' THEN Items ELSE NULL END AS 'BREAKFAST'
, CASE WHEN Typez = 'Lunch' THEN Items ELSE NULL END AS 'LUNCH'
FROM #Customer
)
SELECT distinct CustomerCode, CustomerName,
SUBSTRING(
(
SELECT ','+BREAKFAST AS [text()]
FROM cte_base b1
WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
ORDER BY b1.BREAKFAST
FOR XML PATH('')
), 2, 1000
) [BREAKFAST],
SUBSTRING(
(
SELECT ','+LUNCH AS [text()]
FROM cte_base b1
WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
ORDER BY b1.LUNCH
FOR XML PATH('')
), 2, 1000
) [LUNCH]
FROM cte_base b2
This can be useful too
create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test
returns
Peter,Paul,Mary
in Postgres - array_agg
SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;
OR STRING_AGG
SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;
Here is the complete solution to achieve this:
-- Table Creation
CREATE TABLE Tbl
( CustomerCode VARCHAR(50)
, CustomerName VARCHAR(50)
, Type VARCHAR(50)
,Items VARCHAR(50)
)
insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'
-- function creation
GO
CREATE FUNCTION [dbo].[fn_GetItemsByType]
(
@CustomerCode VARCHAR(50)
,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE ( Items VARCHAR(5000) )
AS
BEGIN
INSERT INTO @ItemType(Items)
SELECT STUFF((SELECT distinct ',' + [Items]
FROM Tbl
WHERE CustomerCode = @CustomerCode
AND Type=@Type
FOR XML PATH(''))
,1,1,'') as Items
RETURN
END
GO
-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type)
from Tbl
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
from
(
select
distinct CustomerCode
,CustomerName
,Type
,F.Items
FROM Tbl T
CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
) x
pivot
(
max(Items)
for Type in (' + @cols + ')
) p '
execute(@query)
If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one.
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
You need to create a variable that will hold your final result and select into it, like so.
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
This worked for me (SqlServer 2016):
SELECT CarNamesString = STUFF((
SELECT ',' + [Name]
FROM tbl_cars
FOR XML PATH('')
), 1, 1, '')
Here is the source : https://www.mytecbits.com/
And a solution for MySql (since this page show up in Google for MySql)
SELECT [Name],
GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',')
FROM tbl_cars
From MySql Documentations
This answer will require some privilege in server to work.
Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one
If you want, I have already created the assembly, and it is possible to download the DLL here.
Once you have downloaded it, you will need to run the following script in your SQL Server:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;
CREATE Assembly concat_assembly
AUTHORIZATION dbo
FROM '<PATH TO Concat.dll IN SERVER>'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.concat (
@Value NVARCHAR(MAX)
, @Delimiter NVARCHAR(4000)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO
sp_configure 'clr enabled', 1;
RECONFIGURE
Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:
SELECT dbo.Concat(field1, ',')
FROM Table1
Hope it helps!!!
UPDATE:
Since MS-SQL 2017 it is possible to use the STRING_AGG function
Oracle 11g Release 2 supports the LISTAGG function. Documentation here.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.
For Oracle DBs, see this question: How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
The best answer appears to be by @Emmanuel, using the built-in LISTAGG() function, available in Oracle 11g Release 2 and later.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
as @user762952 pointed out, and according to Oracle's documentation http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php, the WM_CONCAT() function is also an option. It seems stable, but Oracle explicitly recommends against using it for any application SQL, so use at your own risk.
Other than that, you will have to write your own function; the Oracle document above has a guide on how to do that.
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
@Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
This puts the stray comma at the beginning.
However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).
You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because Google doesn't do work stuff at home :-)
In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
Although it's too late, and already has many solutions. Here is simple solution for MySQL:
SELECT t1.id,
GROUP_CONCAT(t1.id) ids
FROM table t1 JOIN table t2 ON (t1.id = t2.id)
GROUP BY t1.id
With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.
Below is an example that uses SQL Server "Information_Schema.Columns" table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.
SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
With a recursive query you can do it:
-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;
-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');
-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank where rang=1
union all
select f0.*, cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1
)
select AllName from tmpRecursive
where rang=NbRow
I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
Although it's too late, and already has many solutions. Here is simple solution for MySQL:
SELECT t1.id,
GROUP_CONCAT(t1.id) ids
FROM table t1 JOIN table t2 ON (t1.id = t2.id)
GROUP BY t1.id
One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".
--SQL Server 2005+
CREATE TABLE dbo.Students
(
StudentId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);
CREATE TABLE dbo.Subjects
(
SubjectId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);
CREATE TABLE dbo.Schedules
(
StudentId INT
, SubjectId INT
, CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
, CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
, CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);
INSERT dbo.Students (StudentId, Name) VALUES
(1, 'Mary')
, (2, 'John')
, (3, 'Sam')
, (4, 'Alaina')
, (5, 'Edward')
;
INSERT dbo.Subjects (SubjectId, Name) VALUES
(1, 'Physics')
, (2, 'Geography')
, (3, 'French')
, (4, 'Gymnastics')
;
INSERT dbo.Schedules (StudentId, SubjectId) VALUES
(1, 1) --Mary, Physics
, (2, 1) --John, Physics
, (3, 1) --Sam, Physics
, (4, 2) --Alaina, Geography
, (5, 2) --Edward, Geography
;
SELECT
sub.SubjectId
, sub.Name AS [SubjectName]
, ISNULL( x.Students, '') AS Students
FROM
dbo.Subjects sub
OUTER APPLY
(
SELECT
CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
+ stu.Name
FROM
dbo.Students stu
INNER JOIN dbo.Schedules sch
ON stu.StudentId = sch.StudentId
WHERE
sch.SubjectId = sub.SubjectId
ORDER BY
stu.Name
FOR XML PATH('')
) x (Students)
;
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
@Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
You need to create a variable that will hold your final result and select into it, like so.
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
A ready-to-use solution, with no extra commas:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
An empty list will result in NULL value. Usually you will insert the list into a table column or program variable: adjust the 255 max length to your need.
(Diwakar and Jens Frandsen provided good answers, but need improvement.)
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
This puts the stray comma at the beginning.
However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).
You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because Google doesn't do work stuff at home :-)
To avoid null values you can use CONCAT()
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".
In SQL Server vNext this will be built in with the STRING_AGG function, read more about it here: https://msdn.microsoft.com/en-us/library/mt790580.aspx
In Oracle, it is wm_concat. I believe this function is available in the 10g release and higher.
This answer will require some privilege in server to work.
Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one
If you want, I have already created the assembly, and it is possible to download the DLL here.
Once you have downloaded it, you will need to run the following script in your SQL Server:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;
CREATE Assembly concat_assembly
AUTHORIZATION dbo
FROM '<PATH TO Concat.dll IN SERVER>'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.concat (
@Value NVARCHAR(MAX)
, @Delimiter NVARCHAR(4000)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO
sp_configure 'clr enabled', 1;
RECONFIGURE
Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:
SELECT dbo.Concat(field1, ',')
FROM Table1
Hope it helps!!!
UPDATE:
Since MS-SQL 2017 it is possible to use the STRING_AGG function
In SQL Server 2005 and later, use the query below to concatenate the rows.
DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t ) t
@User1460901 You can try something like this:
WITH cte_base AS (
SELECT CustomerCode, CustomerName,
CASE WHEN Typez = 'Breakfast' THEN Items ELSE NULL END AS 'BREAKFAST'
, CASE WHEN Typez = 'Lunch' THEN Items ELSE NULL END AS 'LUNCH'
FROM #Customer
)
SELECT distinct CustomerCode, CustomerName,
SUBSTRING(
(
SELECT ','+BREAKFAST AS [text()]
FROM cte_base b1
WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
ORDER BY b1.BREAKFAST
FOR XML PATH('')
), 2, 1000
) [BREAKFAST],
SUBSTRING(
(
SELECT ','+LUNCH AS [text()]
FROM cte_base b1
WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
ORDER BY b1.LUNCH
FOR XML PATH('')
), 2, 1000
) [LUNCH]
FROM cte_base b2
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
@Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
There are couple ways in oracle,
create table name
(first_name varchar2(30));
insert into name values ('Peter');
insert into name values ('Paul');
insert into name values ('Mary');
Solution is 1:
select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
o/p=> Peter,Paul,Mary
Soution is 2:
select rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
o/p=> Peter,Paul,Mary
In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
Depends on your database vendor. MySQL has concat_ws. MS SQL Server expects you to do it in your client application.
Update: you could also do it in an external procedure or UDF, perhaps by using a cursor or calling out to CLR code.
One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".
Oracle 11g Release 2 supports the LISTAGG function. Documentation here.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
This puts the stray comma at the beginning.
However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).
You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because Google doesn't do work stuff at home :-)
I really liked elegancy of Dana's answer. Just wanted to make it complete.
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
A ready-to-use solution, with no extra commas:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
An empty list will result in NULL value. Usually you will insert the list into a table column or program variable: adjust the 255 max length to your need.
(Diwakar and Jens Frandsen provided good answers, but need improvement.)
In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
Source: Stackoverflow.com