Search code examples
sqlsql-servert-sqlsql-server-2017

How to use STRING_AGG to group some value into one line?

I wanted to group several lignes into one when all the columns are identicals but one. This is an example :

ID  |  Name  | CP  | Job
-----------------------------
1   |  Muse  | 13  | Job1
1   |  Muse  | 13  | Job2
1   |  Muse  | 13  | Job3
2   |  Tort  | 51  | Job4
2   |  Tort  | 51  | Job5

I want to have : 

ID  |  Name  | CP  | Job
-----------------------------
1   |  Muse  | 13  | Job1, Job2, Job3
2   |  Tort  | 51  | Job4, Job5

I've tried to use STRING_AGG this way :

SELECT ID, Name, CP, STRING_AGG(Job, ',')
FROM myTable
GROUP BY ID, Name, CP, Job
ORDER BY ID

I've read this : https://database.guide/how-to-return-query-results-as-a-comma-separated-list-in-sql-server/

Thanks for your help

Solution

  • Actually, I've just found out by myself.... I needed to NOT group by the Job :

    SELECT ID, Name, CP, STRING_AGG(Job, ',')
FROM myTable
GROUP BY ID, Name, CP
ORDER BY ID