CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017
ordiminnie
ordiminnie

Reputation: 127

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

Upvotes: 0

Views: 1645

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

Just an interesting observation and something to know about using STRING_AGG. If you need the items in your new job column returned in any specific order you would leverage WITHIN GROUP. 

-- Sample data with the inserts rearranged in random order
DECLARE @myTable TABLE 
(
  ID     INT,
  [Name] VARCHAR(100),
  CP     INT,
  Job    VARCHAR(100)
);

INSERT @myTable
VALUES
(1,'Muse',13,'Job3'),
(1,'Muse',13,'Job1'),
(1,'Muse',13,'Job2'),
(2,'Tort',51,'Job5'),
(2,'Tort',51,'Job4');

-- STRING_AGG ordering NOT guaranteed
SELECT ID, Name, CP, Job = STRING_AGG(Job, ',')
FROM @myTable
GROUP BY ID, Name, CP
ORDER BY ID;

-- STRING_AGG ordering IS guaranteed    
SELECT ID, Name, CP, job = STRING_AGG(Job, ',') WITHIN GROUP (ORDER BY Job)
FROM @myTable
GROUP BY ID, Name, CP
ORDER BY ID;

Un-ordered STRING_AGG Results:

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

Un-ordered STRING_AGG Results: 

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

Upvotes: 1

ordiminnie
ordiminnie

Reputation: 127

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

Upvotes: 3

Related Questions