Query for Comma-Separated Ids to Comma-Separated Values

How can I pull a list of ID's from a SQL table as a comma-separated values string?

In addition to @OMG Ponies method, you could also try this COALESCE trick from:

Using COALESCE to Build Comma-Delimited Strings

declare @string nvarchar(255)

select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products

Convert Comma separated ids into its assigned values

For a table containing the languages like this:

CREATE TABLE languages (
  id INTEGER,
  name VARCHAR(20)
);

INSERT INTO languages
  (id, name)
VALUES
  ('1', 'English'),
  ('2', 'Germany'),
  ('3', 'Spanish'),
  ('4', 'Hindi'),
  ('5', 'Arabic');

you can join the tables, group by StaffID and use string_agg():

select
  t.StaffID, 
  string_agg(l.name, ',') within group (order by l.id) LanguagesSpoken
from tablename t inner join languages l
on concat(',', t.languagesspoken, ',') like concat('%,', l.id, ',%')
group by t.StaffID

See the demo.

Results:

> StaffID | LanguagesSpoken        
> ------: | :----------------------
>       1 | English,Germany,Spanish
>       2 | Spanish,Hindi          
>       3 | Germany,Arabic   

How to convert list of comma separated Ids into their name?

WITH data AS (
    SELECT * FROM (VALUES (1, '10,15'), (2, NULL)) x(id, task_ids)
),
task AS (
    SELECT * FROM (VALUES ('10', 'a'), ('15', 'b')) x(id, task_name)
)
SELECT
    d.id, d.task_ids
    -- array_agg will obviously capture NULL task_name comping from LEFT JOIN, so we need to filter out such results
    IF(array_agg(t.task_name) IS NOT DISTINCT FROM ARRAY[NULL], NULL, array_agg(t.task_name)) task_names
FROM data d
-- split task_ids by `,`, convert into numbers, UNNEST into separate rows
LEFT JOIN UNNEST (split(d.task_ids, ',')) AS e(task_id) ON true
-- LEFT JOIN with task to pull the task name
LEFT JOIN task t ON e.task_id = t.id
-- aggregate back
GROUP BY d.id, d.task_ids;

Multiple rows to one comma-separated value different ID

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

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.

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

How do I get a comma separated list of ten values and not all of them?

Use SUBSTRING_INDEX() with 10 as the 3d argument:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(n.id), ',', 10) FROM tbl_names n

---

Related Topics