none
T-SQL Equivalent For Group_Concat() Function

    Question

  • 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

    Thursday, September 17, 2009 7:49 PM

Answers

  • Try:

    SELECT
        G.GROUP_ID,
        G.GROUP_NAME,
        stuff(
        (
        select cast(',' as varchar(max)) + U.USERNAME
        from USER_GROUPS U
        WHERE U.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
                          
                  
    Friday, September 18, 2009 12:15 AM
    Moderator

All replies

  • 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 (My Blog)
    • Proposed as answer by SQLSmallTalk Saturday, February 23, 2013 12:17 AM
    • Unproposed as answer by SQLSmallTalk Saturday, February 23, 2013 1:40 AM
    Thursday, September 17, 2009 7:57 PM
    Moderator
  • 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]
    Friday, September 18, 2009 12:09 AM
  • Try:

    SELECT
        G.GROUP_ID,
        G.GROUP_NAME,
        stuff(
        (
        select cast(',' as varchar(max)) + U.USERNAME
        from USER_GROUPS U
        WHERE U.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
                          
                  
    Friday, September 18, 2009 12:15 AM
    Moderator
  • --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
    */

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Friday, September 18, 2009 1:00 AM
  • Hunchback,

    Thanks for reply that worked awesomely.
    Friday, September 18, 2009 1:18 AM
  • 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...

    Friday, September 10, 2010 6:19 PM
  • 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!
    Thursday, December 01, 2011 5:34 AM
  • 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. 
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, December 01, 2011 3:49 PM
  • Awesome bit of code, very useful, thanks!
    Thursday, February 23, 2012 2:24 PM
  • 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.

    Thursday, August 16, 2012 2:26 PM
  • 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.


    {-|-}

    Tuesday, April 02, 2013 4:46 PM