Comma separated results in SQL
Update (As suggested by @Aaron in the comment)
STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.
SELECT
STUDENTNUMBER
, STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
, STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
FROM Education E
GROUP BY E.STUDENTNUMBER;
Original Answer:
Use FOR XML PATH('')
- which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result
SELECT
STUFF((SELECT ',' + INSTITUTIONNAME
FROM EDUCATION EE
WHERE EE.STUDENTNUMBER = E.STUDENTNUMBER
ORDER BY sortOrder
FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)')
, 1, LEN(','), '') AS XmlPathList
FROM EDUCATION E
GROUP BY E.STUDENTNUMBER
Here is the FIDDLE showing results for both STRING_AGG
and FOR XML PATH('')
.
How do I create a comma-separated list using a SQL query?
There is no way to do it in a DB-agnostic way.
So you need to get the whole data-set like this:
select
r.name as ResName,
a.name as AppName
from
Resouces as r,
Applications as a,
ApplicationsResources as ar
where
ar.app_id = a.id
and ar.resource_id = r.id
And then concat the AppName programmatically while grouping by ResName.
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 ║
╚════╩═════════════════════╝
SQL Server - join rows into comma separated list
You are missing the condition inside the sub query.
SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1 where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id
Demo
comma delimited list as a single string, T-SQL
You could use FOR XML PATH
and STUFF
to concatenate the multiple rows into a single row:
select distinct t1.id,
STUFF(
(SELECT ', ' + convert(varchar(10), t2.date, 120)
FROM yourtable t2
where t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') AS date
from yourtable t1;
See SQL Fiddle with Demo
Where IN a Comma delimited string
Create some split string function and convert the comma separated values to rows then you can use the converted rows IN
clause
DECLARE @List VARCHAR(max)
SELECT @List = COALESCE(@List + ',', '') +StaffCode
FROM tblStaffs
SELECT UserName
FROM #temptable
WHERE #temptable.StaffCode IN (SELECT split_values
FROM dbo.Splitstring_function(@list))
Check here for various Split String function
If you dont want to create functions then you can also directly use the code instead of creating a new function(M.Ali's answer).
Another way of doing it is using dynamic query
.
Declare @List varchar(max), @sql nvarchar(max)
Select @List = coalesce(@List + ',','') + '''' + StaffCode + ''''
From tblStaffs
set @sql = '
Select UserName
From #temptable
Where #temptable.StaffCode IN ('+ @List + ')'
--print @sql
exec (@sql)
To debug the dynamic query always print
the dynamic sql before executing.
How to run select query on columns having comma separated values
Use the operator LIKE
:
SELECT USER
FROM DETAILS
WHERE ',' || DEPARTMENT || ',' LIKE '%,' || 'Admin' || ',%'
AND ',' || DEPARTMENT || ',' LIKE '%,' || 'Finance' || ',%'
AND ',' || DEPARTMENT || ',' NOT LIKE '%,' || 'Accounts' || ',%';
Or the function INSTR()
:
SELECT USER
FROM DETAILS
WHERE INSTR(',' || DEPARTMENT || ',', ',' || 'Admin' || ',') > 0
AND INSTR(',' || DEPARTMENT || ',', ',' || 'Finance' || ',') > 0
AND INSTR(',' || DEPARTMENT || ',', ',' || 'Accounts' || ',') = 0;
This will work if there are no spaces after each comma in the column DEPARTMENT
.
See the demo.
Related Topics
Performance Issue in Update Query
Remove Uniqueness of Index in Postgresql
Select Top N Rows for Each Group
SQL Management Studio Won't Recognize a Table Exists After Scripted Create
Postgresql Constraint - Only One Row Can Have Flag Set
Compress Rows with Nulls and Duplicates into Single Rows
How to Represent a Many-To-Many Relationship in a Relational Database
Which Table Exactly Is the "Left" Table and "Right" Table in a Join Statement (Sql)
Postgresql Get a Random Datetime/Timestamp Between Two Datetime/Timestamp
What Is Wrong with a Transitive Dependency
Has_Many :Through Multiple Has_One Relationships
MySQL Myisam Table Performance Problem Revisited
Insert Default Value When Parameter Is Null