How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
DECLARE @XYList varchar(MAX)
SET @XYList = ''
SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS
-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)
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;
Result Set
╔════╦═════════════════════╗
║ ID ║ List_Output ║
╠════╬═════════════════════╣
║ 1 ║ 100, 200, 300, 400 ║
╚════╩═════════════════════╝
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))
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
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.
Concatenate many rows into comma-separated list based on unique id
In Oracle you would do this with a LISTAGG function, but for SQL-Server try this question, there's a few different options there:
ListAGG in SQLSERVER
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 select records that occurs 3 times and combine multiple rows into a comma-delimited list in sql server 2005
Hope this example will help:
DECLARE @people TABLE(id INT, NAME NVARCHAR(MAX))
INSERT @people VALUES (1, 'John'), (2, 'Mary')
DECLARE @cars TABLE(ownerId INT, NAME NVARCHAR(MAX))
INSERT @cars VALUES (1, 'Toyota'), (1, 'Lada'), (2, 'Nissan'), (2, 'Mazda'), (2, 'Buick')
SELECT
Id,
Name,
STUFF((
SELECT ','+name
FROM @Cars c
WHERE c.OwnerId = p.Id
FOR XML PATH('')
),1,1,'') AS OwnedCars
FROM @people p
Regarding your case:
;WITH Students(Id, Name) AS
(
SELECT DISTINCT stu_id, s_name
FROM Student
)
SELECT
Id,
Name,
STUFF((
SELECT ','+s_subject
FROM student
WHERE student.stu_id = Students.Id
FOR XML PATH('')
),1,1,'') AS s_subjects,
STUFF((
SELECT ','+CAST(marks AS varchar)
FROM student
WHERE student.stu_id = Students.Id
FOR XML PATH('')
),1,1,'') AS marks
FROM Students
Related Topics
Is SQL or Even Tsql Turing Complete
Are a Case Statement and a Decode Equivalent
Jpql in Clause: Java-Arrays (Or Lists, Sets...)
Calculate Business Days in Oracle SQL(No Functions or Procedure)
SQL Query to Select Distinct Row with Minimum Value
Easiest Way to Populate a Temp Table with Dates Between and Including 2 Date Parameters
SQL Where Id in (Id1, Id2, ..., Idn)
Create Table with Sequence.Nextval in Oracle
How to Pass Parameters to a View in SQL
What's the Difference Between Rank() and Dense_Rank() Functions in Oracle
What Happens to an Uncommitted Transaction When the Connection Is Closed
Finding Similar Strings with Postgresql Quickly
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
Postgresql Create Table If Not Exists
How to Convert Comma Separated Nvarchar to Table Records in SQL Server 2005
How to Have Nhibernate Only Generate the SQL Without Executing It