MYSQL - GROUP_CONCAT results of UNION ALL into one row

Question

I have the following statement:

SELECT DISTINCT Concat("table1.", column_name) 
FROM   information_schema.columns 
WHERE  table_name = "table1" 
UNION ALL 
SELECT DISTINCT Concat("table2.", column_name)  
FROM   information_schema.columns 
WHERE  table_name = "table2";

Which produces the following results:

+---------------------------------+
| CONCAT("table1.", column_name)  |
+---------------------------------+
| table1.column1                  |
| table1.column2                  |
| table1.column3                  |
| table2.column4                  |
| table2.column5                  |
| table2.column6                  |
| table2.column7                  |
+---------------------------------+

I would like it to be in the following format:

+-----------------------------------------------------------------------------------------------------------+
| CONCAT("table1.", column_name)                                                                            |
+-----------------------------------------------------------------------------------------------------------+
| table1.column1,table1.column2,table1.column3,table2.column4,table2.column5,table2.column6,table2.column7  |
+-----------------------------------------------------------------------------------------------------------+

I have tried using GROUP_CONCAT like this:

SELECT Group_Concat(DISTINCT Concat("table1.", column_name)) 
FROM   information_schema.columns 
WHERE  table_name = "table1" 
UNION ALL 
SELECT Group_Concat(DISTINCT Concat("table2.", column_name)) 
FROM   information_schema.columns 
WHERE  table_name = "table2";

But this incorrectly produced the following results:

+--------------------------------------------------------------+
| CONCAT("table1.", column_name)                               |
+--------------------------------------------------------------+
| table1.column1,table1.column2,table1.column3                 |
| table2.column4,table2.column5,table2.column6,table2.column7  | 
+--------------------------------------------------------------+

From this I naturally tried to do a GROUP_CONCAT it as a sub query, like so:

SELECT GROUP_Concat(
    SELECT Group_Concat(DISTINCT Concat("table1.", column_name)) 
    FROM   information_schema.columns 
    WHERE  table_name = "table1" 
    UNION ALL 
    SELECT Group_Concat(DISTINCT Concat("table2.", column_name)) 
    FROM   information_schema.columns 
    WHERE  table_name = "table2" t)
FROM t;

But there is a syntax error in the above statement. How would I concatenate the results of UNION ALL into one row?

I have reviewed the following questions without any success:

Combing results from union all into one row when some columns have different values

How do I combine two queries (union all) into one row?

MySQL COUNT results of UNION ALL statement

This is kind of related to my previous question: MySQL - Subquery in SELECT clause

score 3 · Accepted Answer · answered Apr 14 '15 at 15:11

You were almost there, try this:

select group_concat(xxx) from (
  SELECT DISTINCT Concat("table1.", column_name)  as xxx
  FROM   columns 
  WHERE  table_name = "table1" 
  UNION ALL 
  SELECT DISTINCT Concat("table2.", column_name)  
  FROM   columns 
  WHERE  table_name = "table2"
) as src;

to be honest - what dou you need this UNION for ? This would do the same...

SELECT  
  group_concat(distinct concat (table_name, '.', column_name) )
from columns 
WHERE table_name in ('table1', 'table2');

check the fiddle: http://sqlfiddle.com/#!9/d1172

Thank you my good sir! Your second suggestion really simplifies the statement! — Ian2thedv, Apr 14 '15 at 15:15

Alex · Answer 2 · 2015-04-14T15:25:41.023

0

SELECT GROUP_Concat(a) 

FROM (
    SELECT Group_Concat(DISTINCT Concat("table1.", column_name)) as a
    FROM   information_schema.columns 
    WHERE  table_name = "table1" 
    UNION ALL 
    SELECT Group_Concat(DISTINCT Concat("table2.", column_name)) 
    FROM   information_schema.columns 
    WHERE  table_name = "table2" ) t
GROUP BY 1

edited Apr 14 '15 at 15:25

answered Apr 14 '15 at 15:00

Alex

16,739
1
28
51

Thanks for the answer Alex, but I'm still getting `ERROR 1064 (42000): You have an error in your SQL syntax...` – Ian2thedv Apr 14 '15 at 15:14

MYSQL - GROUP_CONCAT results of UNION ALL into one row

2 Answers2