Merging column data

Question

SELECT Name, Members 
FROM People 
WHERE Name LIKE '%Kako%' 
GROUP BY Name, Members

I am getting this result from the SQL query shown above:

Name        |   Members
------------+-------------
James Kako  |   Rangers
James Kako  |   Wind
James Kako  |   Poers
James Kako  |   Lohe
James Kako  |   Jubo
J. Kako     |   Wind
J. Kako     |   Lohe
J. Kako     |   Poers

I would like to merge Members column like this:

Name        | Members
------------+---------------------------------
James Kako  | Rangers, Wind, Poers, Lohe, Jubo
J. Kako     | Wind, Lohe, Poers

Thank you in advance

https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Paul Abbott, Jul 10 '18 at 22:08
Please change the order of `GROUP BY` and `WHERE` in your SQL , respectively. — Barbaros Özhan, Jul 10 '18 at 22:26

Barbaros Özhan · Accepted Answer · 2018-07-10T22:23:55.413

1

Using STRING_AGG is the direct way for this type of problems :

SELECT Name, STRING_AGG(Members, ', ') as Members
  FROM People
 WHERE Name LIKE '%Kako%'
GROUP BY Name
ORDER BY Name DESC;

SQL Fiddle Demo

edited Jul 10 '18 at 22:23

answered Jul 10 '18 at 22:16

Barbaros Özhan

59,113
10
31
55

Teşekkürler. Aslında gerçek veriler çoklu sonuç veriyor. Yani James Kako | Rangers kayıdından onlarca veri var. Bu verileri sub query'ler ile DISTINCT ettim. Acaba önerebileceğiniz daha sağlıklı bir yöntem var mıdır? – Kerberos Jul 10 '18 at 22:50
1

Teşekkür ederim. İyi çalışmalar. – Kerberos Jul 12 '18 at 08:04

score 0 · Answer 2 · answered Jul 11 '18 at 01:00

This solution works as well:

DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + members, members) 
FROM MergeColumn ;
Select name , @char as Mergerd from MergeColumn group by name;

Also, you can take a look at this link for more solutions to the question you asked: How to concatenate text from multiple rows into a single text string in SQL server?

Merging column data

2 Answers2