MySQL Results as comma separated list

Question

I need to run a query like:

SELECT p.id, p.name, 
       (SELECT name 
          FROM sites s 
         WHERE s.id = p.site_id) AS site_list
  FROM publications p

But I'd like the sub-select to return a comma separated list, instead of a column of data. Is this even possible, and if so, how?

Paul Dixon · Accepted Answer · 2017-12-19T19:31:13.380

279

You can use GROUP_CONCAT to perform that, e.g. something like

SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id, p.name;

edited Dec 19 '17 at 19:31

answered Mar 19 '09 at 13:37

Paul Dixon

295,876
54
310
348

10

Also, quick note that if you're using PHPMyAdmin and want to output a comma-delimited list to the page, use `GROUP_CONCAT(CAST(s.name AS CHAR))` or else it will just return something wholly unuseful like `[BLOB - 20 Bytes]`. – devios1 Mar 01 '12 at 15:52
3

The intent is fine and MySQL will allow this, but be careful (generally) with your use of GROUP BY. The items in the select list need to be valid aggregates in the context of the GROUP BY clause. In this case, p.name is not strictly valid. Any database conforming to the SQL standard will treat this as an error. For this case, either use MAX(p.name) in the select list or add p.name to the GROUP BY clause. Since Paul probably means p.id to represent a primary or unique key, adding p.name to the GROUP BY clause will have no impact on the final result. – Jon Armstrong - Xgc Oct 13 '12 at 01:45
Note you may have to set max length for session per http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length – sobelito Mar 20 '15 at 18:55

score 29 · Answer 2 · answered Oct 30 '19 at 09:58

Now only I came across this situation and found some more interesting features around GROUP_CONCAT. I hope these details will make you feel interesting.

simple GROUP_CONCAT

SELECT GROUP_CONCAT(TaskName) 
FROM Tasks;

Result:

+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName)                                           |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+

GROUP_CONCAT with DISTINCT

SELECT GROUP_CONCAT(TaskName) 
FROM Tasks;

Result:

+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName)                                           |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+

GROUP_CONCAT with DISTINCT and ORDER BY

SELECT GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) 
FROM Tasks;

Result:

+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) |
+--------------------------------------------------------+
| Take dog for walk,Relax,Paint roof,Feed cats,Do garden |
+--------------------------------------------------------+

GROUP_CONCAT with DISTINCT and SEPARATOR

SELECT GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') 
FROM Tasks;

Result:

+----------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ')                |
+----------------------------------------------------------------+
| Do garden + Feed cats + Paint roof + Relax + Take dog for walk |
+----------------------------------------------------------------+

GROUP_CONCAT and Combining Columns

SELECT GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ') 
FROM Tasks;

Result:

+------------------------------------------------------------------------------------+
| GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ')                                 |
+------------------------------------------------------------------------------------+
| 1) Do garden 2) Feed cats 3) Paint roof 4) Take dog for walk 5) Relax 6) Feed cats |
+------------------------------------------------------------------------------------+

GROUP_CONCAT and Grouped Results Assume that the following are the results before using GROUP_CONCAT

+------------------------+--------------------------+
| ArtistName             | AlbumName                |
+------------------------+--------------------------+
| Iron Maiden            | Powerslave               |
| AC/DC                  | Powerage                 |
| Jim Reeves             | Singing Down the Lane    |
| Devin Townsend         | Ziltoid the Omniscient   |
| Devin Townsend         | Casualties of Cool       |
| Devin Townsend         | Epicloud                 |
| Iron Maiden            | Somewhere in Time        |
| Iron Maiden            | Piece of Mind            |
| Iron Maiden            | Killers                  |
| Iron Maiden            | No Prayer for the Dying  |
| The Script             | No Sound Without Silence |
| Buddy Rich             | Big Swing Face           |
| Michael Learns to Rock | Blue Night               |
| Michael Learns to Rock | Eternity                 |
| Michael Learns to Rock | Scandinavia              |
| Tom Jones              | Long Lost Suitcase       |
| Tom Jones              | Praise and Blame         |
| Tom Jones              | Along Came Jones         |
| Allan Holdsworth       | All Night Wrong          |
| Allan Holdsworth       | The Sixteen Men of Tain  |
+------------------------+--------------------------+

USE Music;
SELECT ar.ArtistName,
    GROUP_CONCAT(al.AlbumName)
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;

Result:

+------------------------+----------------------------------------------------------------------------+
| ArtistName             | GROUP_CONCAT(al.AlbumName)                                                 |
+------------------------+----------------------------------------------------------------------------+
| AC/DC                  | Powerage                                                                   |
| Allan Holdsworth       | All Night Wrong,The Sixteen Men of Tain                                    |
| Buddy Rich             | Big Swing Face                                                             |
| Devin Townsend         | Epicloud,Ziltoid the Omniscient,Casualties of Cool                         |
| Iron Maiden            | Somewhere in Time,Piece of Mind,Powerslave,Killers,No Prayer for the Dying |
| Jim Reeves             | Singing Down the Lane                                                      |
| Michael Learns to Rock | Eternity,Scandinavia,Blue Night                                            |
| The Script             | No Sound Without Silence                                                   |
| Tom Jones              | Long Lost Suitcase,Praise and Blame,Along Came Jones                       |
+------------------------+----------------------------------------------------------------------------+

it has limitation probably not more than 65 results – ani0904071 Nov 18 '21 at 22:10 — ani0904071, Nov 18 '21 at 22:10

Robert Quinn · Answer 3 · 2013-01-12T20:33:25.743

12

Instead of using group concat() you can use just concat()

Select concat(Col1, ',', Col2) as Foo_Bar from Table1;

edit this only works in mySQL; Oracle concat only accepts two arguments. In oracle you can use something like select col1||','||col2||','||col3 as foobar from table1; in sql server you would use + instead of pipes.

edited Jan 12 '13 at 20:33

answered Dec 29 '12 at 08:44

Robert Quinn

579
5
10

3

This shouldn't work in GROUP BY case, while GROUP_CONCAT() will concatenate contents of a single column – Aram Paronikyan Jun 28 '16 at 10:58

score 3 · Answer 4 · answered Sep 14 '17 at 10:54

In my case i have to concatenate all the account number of a person who's mobile number is unique. So i have used the following query to achieve that.

SELECT GROUP_CONCAT(AccountsNo) as Accounts FROM `tblaccounts` GROUP BY MobileNumber

Query Result is below:

Accounts
93348001,97530801,93348001,97530801
89663501
62630701
6227895144840002
60070021
60070020
60070019
60070018
60070017
60070016
60070015

MySQL Results as comma separated list

4 Answers4

Linked

Related