Question 232589 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Merge rows separated by comma in SQL

I have a table containing as below:

id cat code class
1 A 197 1
2 B 197 2
2 C.1 233 1
2 C.2 240 1

Is there a way I can concatenate the records separated by comma as following

id cat code class
1 A,B 197 1,2

I tried using GROUP_CONCAT() but for some reason, it doesn’t retrieve all the records

SELECT ID, GROUP_CONCAT(CAT),GROUP_CONCAT(Class),code FROM T1 GROUP BY code
Tags:

2

Answers


  1. 0

    Try the following query:

    SELECT
      ID
    , GROUP_CONCAT(CAT) as CAT
    , CODE
    , GROUP_CONCAT(CLASS) as CLASS
FROM T1 
GROUP BY
      ID
    , CODE

    You should group by the "non-aggregating columns" (which are ID and CODE)

    Login or Signup to reply.
  2. 0 
    SELECT MIN(id) AS id,    -- if you need another criteria for `id` selection
                         -- then edit accordingly
       GROUP_CONCAT(cat ORDER BY cat) AS cats, 
       GROUP_CONCAT(class ORDER BY cat) AS classes,
       code 
FROM t1 
GROUP BY code;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search