How to Create a Comma-Separated List Using a SQL Query

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.

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 listStr

FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE

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.

How to create a comma delimited list using SELECT statement from table columns

Use STUFF and FOR XML PATH with correlated subquery:

select 
    class_id,
    stuff(
        (select ', ' + Instructor_Name from your_table b 
         where a.class_id = b.class_id for xml path('')),
        1, 2, ''
    ) names
from your_table a
group by
    class_id;

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

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 ║
╚════╩═════════════════════╝

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.

Get comma-separated set of values from table where another reference value on another table appears twice (or more)

Nice starting fiddle, thanks! If we just take what you already have and put it in a CTE, we can write a standard string aggregation around it:

;WITH subs AS 
(
  SELECT prod.CODE, sub.SUBSTITUTECODE
  FROM @SUBSTITUTE AS sub
  INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
  WHERE sub.SUBSTITUTECODE IN (SELECT sub.SUBSTITUTECODE
    FROM @SUBSTITUTE AS sub
    INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
    GROUP BY sub.SUBSTITUTECODE
    HAVING COUNT(sub.SUBSTITUTECODE) > 1)
)
SELECT CODES = STUFF((SELECT ',' + CODE 
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE FROM subs
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

But we can simplify this code slightly, most importantly to avoid referencing both tables twice, like this:

;WITH subs AS
(
  SELECT s.ITEID, s.SUBSTITUTECODE, m.CODE, 
    c = COUNT(*) OVER (PARTITION BY s.SUBSTITUTECODE)
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
)
SELECT CODES = STUFF((SELECT ',' + CODE
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE
  FROM subs 
  WHERE c > 1 
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

Note that on more modern versions of SQL Server (2017+), STRING_AGG() makes this much easier:

SELECT CODES = STRING_AGG(m.CODE, ','), s.SUBSTITUTECODE
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
  GROUP BY s.SUBSTITUTECODE
  HAVING COUNT(*) > 1;

• Example db<>fiddle

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

---

Related Topics