Convert multiple rows into one with comma as separator
This should work for you. Tested all the way back to SQL 2000.
create table #user (username varchar(25))
insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')
declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user
select SUBSTRING(@tmp, 0, LEN(@tmp))
Can I Comma Delimit Multiple Rows Into One Column?
Here is a solution that works in SQL Server 2005+:
SELECT t.TicketID,
STUFF(ISNULL((SELECT ', ' + x.Person
FROM @Tickets x
WHERE x.TicketID = t.TicketID
GROUP BY x.Person
FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') [No Preceeding Comma],
ISNULL((SELECT ', ' + x.Person
FROM @Tickets x
WHERE x.TicketID = t.TicketID
GROUP BY x.Person
FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), '') [Preceeding Comma If Not Empty]
FROM @Tickets t
GROUP BY t.TicketID
Reference:
- STUFF (Transact-SQL)
How to show data from multiple rows into single row as comma separated string
Modification:
If your SQL Server version is 2017 or above then you can use string_agg() to achieve your desired result. It's way too much faster than stuff() with For XML PATH()
with cte as
(SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories
INNER JOIN Books ON Books.Id = Books_Categories.BookId
INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
SELECT t.BookName,STRING_AGG(CategoryName,',') CategoryName
FROM cte t
GROUP BY t.BookName
You can achieve this in sql-server older than 2017 by using STUFF() with For XML PATH
Schema:
CREATE TABLE [dbo].[books](
[id] [int] NULL,
[name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[categories](
[id] [int] NULL,
[name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Books_Categories](
[id] [int] NULL,
[bookid] [int] NULL,
[categoryid] [int] NULL
) ON [PRIMARY]
Insert statements:
insert into books values(1,'BookA');
insert into books values(2,'BookB');
insert into categories values(1,'CategoryA');
insert into categories values(2,'CategoryB');
insert into Books_Categories values(1, 1, 1);
insert into Books_Categories values(2, 1, 2);
insert into Books_Categories values(3, 2, 2);
Query#1 (STUFF() and For XML PATH():
with cte as
(SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories
INNER JOIN Books ON Books.Id = Books_Categories.BookId
INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
SELECT t.BookName
, STUFF(( SELECT ', ' + CategoryName
FROM cte
WHERE BookName = t.BookName
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'') AS CategoryName
FROM cte t
GROUP BY t.BookName
GO
Query#2 (using string_agg() for SQL Server 2017(14x) and later):
with cte as
(SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories
INNER JOIN Books ON Books.Id = Books_Categories.BookId
INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
select t.bookname,string_agg(categoryname,',') CategoryName from cte t
GROUP BY t.BookName
Output:
BookName | CategoryName |
---|---|
BookA | CategoryA, CategoryB |
BookB | CategoryB |
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;
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
Concatenate few rows into a single row with comma seperated using string_agg in postgreSQL
Give this a shot:
select visit_id, string_agg(page_name, ',' order by sequence_number) as cart_items
from events e
join page_hierarchy ph on e.page_id = ph.page_id
join event_identifier ei on ei.event_type = e.event_type
where event_name = 'Add to Cart'
group by visit_id
The order by sequence_number
within the string_agg(...)
function will sort your comma separated output based on sequence number.
Here's an example with the sample data you provided:
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=52077d5be605a51d3a7bb14152a392df
Here're the results of that:
visit_id | cart_items
:------- | :------------
d58cbd | Kingfish,Tuna
How can I combine multiple rows into a comma-delimited list in Oracle?
Here is a simple way without stragg or creating a function.
create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');
SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
COUNT (*) OVER () cnt
FROM countries)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
CSV
--------------------------
Albania,Andorra,Antigua
1 row selected.
As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.
select listagg(country_name,', ') within group(order by country_name) csv
from countries;
CSV
--------------------------
Albania, Andorra, Antigua
1 row selected.
Related Topics
How to Change MySQL Table Names in Linux Server to Be Case Insensitive
Error Related to Only_Full_Group_By When Executing a Query in MySQL
Oracle SQL Query: Retrieve Latest Values Per Group Based on Time
How to Use Returning With on Conflict in Postgresql
How to Query Using Fields Inside the New Postgresql Json Datatype
How to Avoid the "Divide by Zero" Error in Sql
How to For SQL Output Clause to Return a Column Not Being Inserted
Convert Datetime Column from Utc to Local Time in Select Statement
Find a String by Searching All Tables in SQL Server
Commit Data in a MySQL Container
Are Postgresql Column Names Case-Sensitive
Postgresql Group_Concat Equivalent
Difference Between Single and Double Quotes in Sql
Difference Between Join and Inner Join