MySQL – Can You Concatenate Strings From a Column Into a Single Row?

Posted on by

How would one concatenate strings from a column (multiple rows) into a single row using MySQL? I see its possible with MS SQL Server 2005 and above. Any incite into how to achieve this in MySQL would be much appreciated.

MS SQL Server 2005 – Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT Web_Account_ID,
GroupNameConcat = REPLACE(
	(
	SELECT
		Web_Account_Group_Name_ID AS [DATA()]
	FROM
		tblWebAccountGroup WAG
	WHERE
		WAG.Web_Account_ID = WA.Web_Account_ID
	ORDER BY
		Web_Account_Group_Name_ID
            FOR XML PATH ('')
        ), ' ', ',')
FROM tblWebAccounts WA
ORDER BY Web_Account_ID

Query Results Example

MS SQL Server 2005 query example

Source: aspfaq.com – How do I concatenate strings from a column into a single row?

39 thoughts on “MySQL – Can You Concatenate Strings From a Column Into a Single Row?”

  4. Actually – you will need to specify which Web_Account_ID you want there..

    So to be safe:

    SELECT WA.Web_Account_ID,
    group_concat(Web_Account_Group_Name_ID ORDER BY Web_Account_Group_Name_ID)
    FROM
    tblWebAccountGroup WAG
    JOIN
    tblWebAccounts WA ON
    WAG.Web_Account_ID = WA.Web_Account_ID
    GROUP BY WA.Web_Account_ID
    ORDER BY WA.Web_Account_ID

  10. With the aggregate function GROUP_CONCAT(). Very easy.
    You can concatenate multiple columns even, modify the delimiter, and specify ordering.

  12. Couldn’t this be done easier with group by and group_concat?

    SELECT Web_Account_ID, group_concat(Web_Account_Group_Name_ID)
    FROM tblWebAccounts WA
    INNER JOIN tblWebAccountGroup WAG using (Web_Account_ID)
    group by Web_Account_ID
    ORDER BY Web_Account_ID

  13. sorry, didn’t notice that you are doing MSSQL there, I don’t know for sure if it has a comperable function. A quick google suggests it doesn’t

  15. “How would one concatenate strings from a column (multiple rows) into a single row using MySQL?”

    SELECT
    Web_Account_ID
    , GROUP_CONCAT(
    Web_Account_Group_Name_ID
    ORDER BY Web_Account_Group_Name_ID
    )
    FROM tblWebAccountGroup WAG
    INNER JOIN tblWebAccounts WA
    USING (Web_Account_ID)
    GROUP BY Web_Account_ID

    So, GROUP_CONCAT is an ordinary aggregate function available since MySQL 4.1 (Released: 23 October 2004)

    http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

  16. I’m sure I’m not the only one posting this, but:

    SELECT WA.Web_Account_ID, GROUP_CONCAT(WAG.Web_Account_Group_Name_ID)
    FROM tblWebAccounts WA
    INNER JOIN tblWebAccountGroup WAG ON WAG.Web_Account_ID = WA.Web_Account_ID
    GROUP BY WA.Web_Account_ID

    Actually you really only need to query the WAG table for this exact data, but the full query with the join allows you to then get at other information in the WA table if you want it.

    http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

  21. How about:

    SELECT Web_Account_ID, GROUP_CONCAT(Web_Account_Group_Name_ID ORDER BY Web_Account_Group_Name_ID ASC SEPARATOR ‘,’) As GroupNameConcat
    FROM tblWebAccounts JOIN tblWebAccountGroup USING Web_Account_ID
    GROUP BY Web_Account_ID
    ORDER BY Web_Account_ID

  22. Use the GROUP_CONCAT() function

    http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

    mysql> create table t1 (f1 int, f2 int);
    Query OK, 0 rows affected (0.08 sec)
    mysql> insert into t1 values(1,1),(1,3),(4,2),(2,3),(2,1),(3,2),(4,4);
    Query OK, 7 rows affected (0.00 sec)
    Records: 7 Duplicates: 0 Warnings: 0
    mysql> select f1, group_concat(f2) from t1 group by f1;
    +——+——————+
    | f1 | group_concat(f2) |
    +——+——————+
    | 1 | 1,3 |
    | 2 | 3,1 |
    | 3 | 2 |
    | 4 | 2,4 |
    +——+——————+
    4 rows in set (0.03 sec)

  29. Hi Adam,
    have you ever heard of GROUP_CONCAT?

    SELECT Web_Account_ID,
    GROUP_CONCAT(Web_Account_Group_Name_ID)
    FROM tblWebAccounts AS WA
    LEFT JOIN tblWebAccountGroup AS WAG
    ON WAG.Web_Account_ID = WA.Web_Account_ID
    GROUP BY WA.Web_Account_ID
    ORDER BY WA.Web_Account_ID, WAG.Web_Account_Group_Name_ID

    Greets,
    Dennis

  32. You can use the GROUP_CONCAT() function

    SELECT WA.Web_Account_ID, GROUP_CONCAT(WAG.Web_Account_Group_Name_ID) /*Default is comma delimited but you can use different separators even return distinct values*/
    FROM tblWebAccounts WA, tblWebAccountGroup WAG
    WHERE WA.Web_Account_ID = WAG.Web_Account_ID /*Inner Join*/
    GROUP BY WA.Web_Account_ID
    ORDER BY Web_Account_ID

  33. Please have a look on function GROUP_CONCAT() as described in section 11.12.1 of MySQL 5.1 Reference Manual.

  36. Thanks to everyone for the awesome feedback. I never expected such a huge response. You all definitely answered my question.

  37. “I never expected such a huge response.”

    heh

    you would not have gotten such a huge response if you had moderated the first few replies that came in

    when i posted (mine was #30) there were ~no~ previous comments visible

    you gots to get more on da ball dan dat, adam

  38. Just wanted to say thanks for posting this question and leaving all the feedback up! This helped me significantly.

    Each photograph on my site has several descriptive attributes stored in a separate table (e.g. City, State) and linked by the photograph’s unique ID. While adding the ability to search to my site, it was no problem to search for Atlanta OR Georgia, but Atlanta AND Georgia wouldn’t work because only the first attribute field was being returned. Using GROUP_CONCAT to stuff all the attributes in the column into a field worked like a charm.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">