MySQL: multiple column values ​​as 1 row with comma delimited?

Question

Suppose I have a select query:

SELECT * FROM tablename 

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

Does anyone know if it is possible to get a result set with only 1 row with 1 field with values ​​separated by column commas, for example:

 "fieldvalue1, fieldvalue2, fieldvalue3" 

The problem is that I do not know the column names of the table in advance ...

Another problem is that the prepared statement is not suitable, since all this must be executed from the trigger, and MySQL does not allow dynamic cursors / selects inside the trigger.

Answer 1

I did some research and came only if GROUP_CONC is changing 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 the same concatenated row containing column names once for each row of the table, instead of evaluating it as column names for an external select statement and return the actual values.

From what I read in all the forums discussing this issue (and there were many), there really is no way to make this work without prepared statements.

I can only think of one other way to do this, and that will have a highlighted column in each table, where you combine the individual column values ​​into INSERT or UPDATE, so you can just select this one field instead of a full set of fields.

Answer 2

First, to find out the column names in advance, assuming you have a table name, you can get them like any other query:

SHOW COLUMNS FROM your_table

Once you have names you can do:

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

Answer 3

It looks like you have 3 questions:

• Getting a result set with 1 row, 1 field: MYSQL has a CONCAT_WS function that works as follows: SELECT CONCAT_WS(',',Field1,Field2,Field3) This will return "Field1Value, Field2Value, Field3Value"

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

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