MySQL : multiple column values as 1 comma-separated string?

4
votes

Suppose I have a select query like :

SELECT * FROM tablename

and in the table are columns : field1, field2 and field3

Does anyone know if it's possible to get a resultset with only 1 row with 1 field, with comma separated values of the columns, like this :

"fieldvalue1, fieldvalue2, fieldvalue3"

Problem is that I don't know the column names of the table in advance...

Another problem is that a prepared statement is not the way to go, since all this should be done from within a trigger, and MySQL doesn't allow dynamic cursors/selects inside a trigger.

3
mysqlselect
I misunderstood your question, oops. I thought you wanted the column names comma-separated. - BoltClock♦

3 Answers

6
votes

I have done some research and only came as far as GROUP_CONCATenating the column names correctly. But the problem is, that 

SELECT (SELECT GROUP_CONCAT( cols.column_name) FROM (SELECT column_name FROM information_schema.columns WHERE table_name='test_table') as cols) FROM test_table

will return one and the same concatenated string containing the column names, once for each table row, instead of evaluating it as the column names for the outer select statement and returning the actual values.

From what I have read in all of the forums discussing this kind of question (and there were many), there is really no way of getting this to work without prepared statements.

I can only think of one other way to do this, and that would be having a dedicated column on each table, where you concatenate the individual column values on INSERT or UPDATE, so you can simply select this one field instead of the full set of fields.

1
votes

First, to find out the columns' names in advance, assuming that you have the table's name, you can get them as any other query:

SHOW COLUMNS FROM your_table

Once you have the names you can do:

SELECT CONCAT(field1,',',field2,',',field3) AS newField FROM your_table

1
votes

Seems like you have 3 questions here:

  1. Getting a resultset with 1 row, 1 field: MYSQL has a CONCAT_WS function that works like this: SELECT CONCAT_WS(',',Field1,Field2,Field3) That will return "Field1Value, Field2Value, Field3Value"

  2. I'm not sure how you are going to get these column names. Do you need to get them from a sql statement, a string, etc. ? You can get the table names `SHOW COLUMNS FROM tablename'. The Field column will have the column names.

  3. Triggers are available in mysql (added in 5.0.2 I think): http://dev.mysql.com/doc/refman/5.0/en/triggers.html