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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments