Comma Separated Values with SQL Query

Comma Separated values with SQL Query

try this:

SELECT City_Code, 
      Post_Code = 
        STUFF((SELECT ', ' + Post_Code
           FROM your_table b 
           WHERE b.City_Code = a.City_Code 
          FOR XML PATH('')), 1, 2, ''),
      Post_Code_Description=
        STUFF((SELECT ', ' + Post_Code_Description
           FROM your_table b 
           WHERE b.City_Code = a.City_Code 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY City_Code

Select a Value from a table which contains Comma separated values in SQL Server

You can use PATINDEX() function
The PATINDEX() function returns the position of a pattern in a string.

If the pattern is not found, this function returns 0.

SELECT *, X.Order
FROM Table1 X
JOIN Table2 Y
    ON X.ID = Y.ID
WHERE
    X. Person = 'Person3' AND
    PATINDEX ('%,Res7,%',CONCAT(',',Y.Resource,',') )>0
  

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

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 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.

EF SQL query Performance on comma-separated string Ids

If you store Ids as comma separated string - you always have TABLE/INDEX scan. If your table is small it can be enough.

With SecondaryTable table which stores Ids associated with main table there a lot of other plans:

1. You can leave as is and trust or not DB Engine optimiser

query = query.Where(x => x.SecondaryTable.Any(s => s.Id == value));

2. If pair (MainId, Id) is unique. The following query should definitely hit index

var query = 
   from m in query
   from s in m.SecondaryTable.Where(s => s.Id == value)
   select s;

3. If pair (MainId, Id) is NOT unique.

var secondary = db.SecondaryTable.Where(s => s.Id == value);
var mainIds = secondary.Select(s => new { s.MainId }).Distinct();

query = 
   from m in query
   from s in mainIds.Where(s => s.MainId == m.Id)
   select m;

Anyway, better to test and check execution plan.

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;

---

Related Topics