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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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                          ------------------------ ----------------------------------------------------------------------------

[sql] MySQL Results as comma separated list

Examples related to sql

Examples related to mysql

Examples related to concatenation