I want to covert the result row from the select query into a comma-separated string. I have table in which there are 153 columns. the select query is like as follow
SELECT * FROM mytable where id = 3
I want all the resulting 153 columns of a row in a comma-separated string. Is there any trick in MySql?
You can use GROUP_CONCAT function to do that.
SELECT GROUP_CONCAT(col1, col2,..coln) FROM my_table;
EDIT:
get all column names with following query and the substitute that in CONCAT_WS function::
SELECT GROUP_CONCAT(COLUMN_NAME)
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'my_table';
SELECT CONCAT_WS(',', col1, col2, ..., coln)
FROM my_table;
or try:
SET @query1 = CONCAT('
SELECT CONCAT_WS(",", ',(SELECT GROUP_CONCAT(COLUMN_NAME)
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'my_table'),')
FROM tablew_name'
);
PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Group_concat() will give the results from multiple rows, not multiple comumns.
SELECT GROUP_CONCAT(COLUMN_NAME) INTO @cols FROM information_schema.COLUMNS WHERE TABLE_NAME = 'myTable'; SELECT CONCAT_WS(',', @cols) FROM myTable WHERE id = 3; but this is giving me comma seperated name of columns. not values...Any help ?
You can use the concat() function, but you will need to name each column as far as I know.
select concat(col1, ',', col2, ',', col3) from myTable where id=3
The only way that I know of to get all the columns of a table is SHOW COLUMNS FROM table and I guess you could include the results from this into your query but it isn't something that I have ever done before.
