0

I have a SQL Server database. This database includes three tables:

Group

ID (int: primary key)
Name (string)
IsActive (bit)

Product

ID (int: primary key)
Name (string)
Description (string)
PublicID (int)

GroupProductLookup

ID (int: primary key)
PublicID (int)
GroupID (int)

I need a query that will return every group and the list of public IDs of the products that are in the group. If there are no products, null or an empty set will work. An example would be:

| GroupID | Name  | Products  |
|---------|-------|-----------|
| 1       | Alpha | (1, 5, 8) |
| 2       | Beta  | (none)    |
| 3       | Cat   | (2, 7)    |

I know how to query the groups. But, I don't know how to get a list of the product ids that belong in the group. This is what I have so far..

SELECT
  g.ID as 'GroupID',
  g.Name as 'Name',
FROM
  [Group] g

How do I get the products IDs?

Thanks!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dev
  • 181
  • 1
  • 13
  • You want Id values as a comma-separated string? Please post sample data to accompany your expected result, [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Apr 21 '21 at 19:28
  • 1
    [How to concatenate from multiple rows into single string](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Stu Apr 21 '21 at 19:30
  • 1
    Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Charlieface Apr 21 '21 at 21:02

1 Answers1

0

This looks like aggregation using string_agg() with left join:

select g.id, g.name, 
       string_agg(gp.publicid, ',') within group (order by gp.publicid)
from groups g left join
     GroupProductLookup gp
     on gp.groupid = g.id
group by g.id, g.name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786