database - MySQL concatenating all columns

Question

Why can we not concatenate in MySQL using the * keyword?

SELECT concat(*) FROM table

or

SELECT group_concat(*) FROM table

Is there any other way we could access values in a column without explicitly using the columns name?

Answer 1

To concatenate all columns in a table, you can't use the * keyword, but you need to explicitly list all columns:

SELECT CONCAT(col1, col2, col3, ....)
FROM yourtable

or you might want to use CONCAT_WS that will skip null values:

SELECT CONCAT_WS(',', col1, col2, col3, ....)
FROM yourtable

If you don't want to specify all column names manually, you could use a dinamic query. This query will return all column names of your table:

SELECT `column_name` 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtable';

and using GROUP_CONCAT you can obtain a list of all column names:

GROUP_CONCAT(CONCAT('`', column_name, '`'))

quoted, in a comma separated format:

`col1`,`col2`,`col3`,`col4`,...

so now we have all the elements to create our query dinamically:

SELECT
  CONCAT(
    'SELECT CONCAT_WS('',',
    GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
    ') AS all_columns FROM yourtable;')
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtable'
INTO @sql;

this query will set the @sql string to something like:

SELECT CONCAT_WS('', col1, col2, col3, ....) AS all_columns FROM yourtable

and this code will execute it:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.