Hello, I am looking for a way to do the MSSQL T-SQL equivalent of the MySQL aggregate function: group_concat().

We are running SQL 2005 Express. Is there a pure T-SQL way to do this, or if not, a way to create a new custom aggregate function?

EX:

SELECT GROUP_CONCAT(FIRST_NAME) AS STUDENT_LIST FROM STUDENTS GROUP BY TEACHER

OUTPUT:

JOSH,JOEY,MARK,LINDA,PAM,BILL,MIKE,JUSTIN

You can do it like this:

SELECT
Teacher
,StudentList=STUFF((SELECT ','+First_Name FROM Students WHERE Teacher=A.Teacher FOR XML PATH('')) , 1 , 1 , '' )
FROM
Teacher A

Brad,

Still having trouble with this, probably because the example I provided was horrible and not what I am trying to do. Here is what I am trying to do:

[code]
SELECT G.GROUP_ID,
G.GROUP_NAME,
FROM GROUPS G,
USER_GROUPS U
WHERE U.USERNAME = @USERNAME
AND G.GROUP_ID = U.GROUP_ID
ORDER BY G.GROUP_NAME ASC;
[/code]

What I want is a comma seperated list of all users in each group. The user is stored in USER_GROUPS as USERNAME. So what I need is like:

[code]
SELECT G.GROUP_ID,
G.GROUP_NAME,
GROUP_CONCAT(U.USERNAME) AS USERS
FROM GROUPS G,
USER_GROUPS U
WHERE U.USERNAME = @USERNAME
AND G.GROUP_ID = U.GROUP_ID
ORDER BY G.GROUP_NAME ASC;
[/code]

Try:

SELECT
G.GROUP_ID,
G.GROUP_NAME,
stuff(
(
select cast(',' as varchar(max)) + U.USERNAME
from USER_GROUPS U
WHEREU.GROUP_ID = G.GROUP_ID
order by U.USERNAME
for xml path('')
), 1, 1, '')AS USERS
FROM
GROUPS G
ORDER BY
G.GROUP_NAME ASC;


AMB

• Proposed as answer by Brad_SchulzModerator Friday, September 18, 2009 1:02 AM
• Marked as answer by JustinK101 Friday, September 18, 2009 1:18 AM

--sample

--> Test Data: @tb
DECLARE @tb TABLE (no varchar(6),val int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1

--SQL Query:

;WITH Liang AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY val ORDER BY no) - no AS rowid,*
    FROM @tb
),
Liang2 AS
(
    SELECT val,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(no))+'~'+RTRIM(MAX(no))
                           ELSE RTRIM(MIN(no)) END AS flag 
    FROM Liang 
    GROUP BY val,rowid
)
SELECT 
    STUFF((SELECT ',' + flag AS [text()] FROM Liang2 
     WHERE val = A.val ORDER BY ABS(rowid) FOR XML PATH('')),1,1,'') AS no,
    val
FROM Liang2 AS A
GROUP BY val;


/*
no	                        val
------------------------ ------------------
000066,000600	              1
000055,000057,000059~000064	  2
000065	                      3
*/

Hunchback,

Thanks for reply that worked awesomely.

Can anyone help me with MS SQL equivalent code for below MySQL statement.

select group_concat

(Col1 order by field(Col2,8,13,15,53,55,6,73,75,3,42,41,45,44)) as FinalColumn from TestTable

Any help is greatly appriciated... thanks in advance...

Elegant solution by Liang. CTE's (Common Table Expressions) work great. Descent performance, fairly good bang for the buck. Worked perfectly for me. Thanks Liang!

This is called "Violating First Normal Form" and good SQL programmers doi not do this. Display is a front end problem. But I am, sure that you will get a kludge. 

Awesome bit of code, very useful, thanks!

You have to be careful with "Display is a front end problem". Does that mean I shouldn't use SUM() in my SQL either? One of the primary functions of databases is to aggregate and calculate data. I'm not sure that summing a set of strings is any different from summing a set of numbers.

Granted if your SQL becomes too convoluted and you don't document why you're using the complex SQL, you're not helping the situation either.

Thanks Hunchback.

I noticed that I had to add "distinct" in the select; e.g.

SELECT DISTINCT
    G.GROUP_ID,
    G.GROUP_NAME,
    stuff(

and remove the order by.

Sql server has not any inbuilt function  call GROUP_CONACT. But we can create user defined CLR GROUP_CONCAT function. Here is full script. You have to only copy that script and execute in sssms. But you must have sufficient permission to execute it:  

http://www.exacthelp.com/2014/04/groupconcat-aggregate-function-in-sql.html

Write your script something like this:

SELECT

dbo.GROUP_CONCAT(FIRST_NAME,',') AS STUDENT_LIST

FROM STUDENTS

GROUP BY TEACHER

I don't think you understand what first normal form is.