How do I roll up multiple detail rows' columns into a single column?

Question

I have two tables:

table_a:

ID    |    NAME    
===============
1     |    DAN
2     |    RON
3     |    JANE

table_b:

ID    |    TEXT    
===============
2     |    APPLE
2     |    BANANA
2     |    COFFEE

I need to get the results for ID 2. I was thinking that even if I got a string containing the TEXT's values for ID 2 from table_b would be great for example:

ID     |    NAME    |    TEXT
=============================
2      |    RON     | APPLE,BANANA,COFFEE

Is this even possible?

This is for MS SQL Server, but perhaps MySQL has similar features? http://codecorner.galanter.net/2009/06/25/t-sql-string-aggregate-in-sql-server/ — Yuriy Galanter, Dec 18 '12 at 22:36
In PostgreSQL, you use the `array_agg` function: http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent — Andy Lester, Dec 18 '12 at 23:03

RolandoMySQLDBA · Accepted Answer · 2012-12-19T02:05:26.677

SELECT
    A.*,GROUP_CONCAT(B.`TEXT`) `TEXT`
FROM
    table_a A INNER JOIN table_b B USING (ID)
WHERE A.ID=2;

Here is a sample

mysql> DROP TABLE IF EXISTS table_a;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS table_b;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE table_a
    -> (ID int not null auto_increment,
    -> NAME VARCHAR(20),primary key (ID));
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE table_b
    -> (ID int not null,`TEXT` TEXT,key (ID));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO table_a (NAME) VALUES ('DAN'),('RON'),('JANE');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO table_b (ID,`TEXT`) VALUES
    -> (2,'APPLE'),(2,'BANANA'),(2,'COFFEE');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,GROUP_CONCAT(B.`TEXT`) `TEXT`
    -> FROM table_a A INNER JOIN table_b B USING (ID) WHERE A.ID=2;
+----+------+---------------------+
| ID | NAME | TEXT                |
+----+------+---------------------+
|  2 | RON  | APPLE,BANANA,COFFEE |
+----+------+---------------------+
1 row in set (0.00 sec)

Give it a Try !!!

To get all values available in table_a and anything that can be grouped from table_b:

SELECT
    A.*,IFNULL(GROUP_CONCAT(B.`TEXT`),'') `TEXT`
FROM
    table_a A LEFT JOIN table_b B USING (ID)
GROUP BY A.ID

Here is that sample:

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS table_a;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS table_b;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE table_a (ID int not null auto_increment,NAME VARCHAR(20),primary key (ID));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE table_b (ID int not null,`TEXT` TEXT,key (ID));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table_a (NAME) VALUES ('DAN'),('RON'),('JANE');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO table_b (ID,`TEXT`) VALUES (2,'APPLE'),(2,'BANANA'),(2,'COFFEE');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,IFNULL(GROUP_CONCAT(B.`TEXT`),'') `TEXT`
    -> FROM table_a A LEFT JOIN table_b B USING (ID) GROUP BY A.ID;
+----+------+---------------------+
| ID | NAME | TEXT                |
+----+------+---------------------+
|  1 | DAN  |                     |
|  2 | RON  | APPLE,BANANA,COFFEE |
|  3 | JANE |                     |
+----+------+---------------------+
3 rows in set (0.00 sec)

mysql>

this is only applicable if you want to get **only one** ID. but if you want two or more, this will give you **invalid** result because you didn't `GROUP` the records by `ID`. — John Woo, Dec 19 '12 at 01:13
Thanks @RolandoMySQLDBA! - everybody, this guy is an angel in disguise — Ted, Jan 17 '13 at 13:01

John Woo · Answer 2 · 2012-12-19T01:13:55.137

2

you only need to use GROUP_CONCAT

SELECT  a.ID, a.Name, GROUP_CONCAT(b.text) textList
FROM    table_a a
        INNER JOIN table_b b
            ON a.ID = b.ID
-- WHERE ...if you want extra condition.
GROUP BY a.ID, a.Name

edited Dec 19 '12 at 01:13

answered Dec 18 '12 at 22:36

John Woo

258,903
69
498
492

mellamokb · Answer 3 · 2012-12-20T19:17:33.140

1

You can use the GROUP_CONCAT function in MySQL to combine results from a related table:

select
  a.id,
  a.name,
  group_concat(b.text) text
from
  table_a a
join
  table_b b on a.id = b.id
group by a.id

Sample Output:

| ID | NAME |         TEXT         |
------------------------------------
|  2 |  RON |  APPLE,BANANA,COFFEE |

Demo: http://www.sqlfiddle.com/#!2/e3584/9

edited Dec 20 '12 at 19:17

answered Dec 18 '12 at 22:36

mellamokb

56,094
12
110
136

this query is only applicable and limited on this case, but if you have other IDs that has matched on the other table, this query will fail because you didn't specify the group by clause. see here: http://www.sqlfiddle.com/#!2/bd59f/1 – John Woo Dec 19 '12 at 01:19

How do I roll up multiple detail rows' columns into a single column?

3 Answers3