Concat multiple rows with group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Concat multiple rows with group by

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-01-17 : 11:09:27

Hi All,

I've got a table named Person which contains a Person_id (PK), Name and Gender.
I want to concat all names to one line grouped by gender, for example:

Gender Name
Male John, Adam
Female Ann, Josephine

I tried with the FOR XML function but it won't work. Can somebody help me?

Tnx.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-01-17 : 11:18:16

What did you try? Use something like shown below:

CREATE TABLE #tmp(gender CHAR(1), NAME VARCHAR(32));
INSERT INTO #tmp VALUES ('M','Joe'),('F','Jane'),('F','Mary'),('F','Liz'),('M','Tom');
SELECT
	gender,
	STUFF(NAMES,1,1,'') AS NAMES
FROM
	(SELECT DISTINCT gender FROM #tmp) a
	CROSS APPLY
	( SELECT ',' + name FROM #tmp b WHERE b.gender = a.gender FOR XML PATH('')) b(NAMES);
DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources