1

I have a table in Database as below :

Id  Name
1   Item1
1   Item2
2   Item3
1   Item4
3   Item5

I need output as below(3rd column is count):

1   Item1,Item2,Item4   3
2   Item3               1
3   Item5               1

How it can achieved by SQL Query ?

Mayank
  • 337
  • 2
  • 9
  • 22

3 Answers3

11

SQL Server has STUFF() function which could able to help you.

SELECT t.Id,
       Name = STUFF( (SELECT DISTINCT ','+Name 
                      FROM table 
                      WHERE Id = t.Id 
                      FOR XML PATH('')
                     ), 1, 1, ''
                   ) 
FROM table t
GROUP BY t.Id;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • STUFF Worked for me..Thanx – Mayank Feb 26 '18 at 08:53
  • The [`STUFF`](https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16) function has nothing to do with rows to column. It just helps in manipulating the string. It's [`FOR XML`](https://learn.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver16) clause that makes this magic. See [How Stuff and 'For Xml Path' work in SQL Server?](https://stackoverflow.com/a/31212160/1369235) – Himanshu Jul 24 '23 at 12:17
5

SQL Server 2017 has introduced a much easier way to achieve this using STRING_AGG(expression, separator).

Here's an example:

SELECT STRING_AGG(T.Name, ', ') FROM MyTable T where MyColumnID = 78

You could even play around with formatting in other ways like this one:

SELECT STRING_AGG(CONCAT(T.MyColumnID,' - ',T.Name), ', ') FROM MyTable T where MyColumnID = 78

More info in this blog I found about it: https://database.guide/the-sql-server-equivalent-to-group_concat/

gbdavid
  • 1,639
  • 18
  • 40
0
select id, group_concat(name) csv,
from Table
group by id
Leonid Usov
  • 1,508
  • 12
  • 16