MySQL : select/join on strings as fieldnames - is this possible?
- by Dylan
I can retrieve all column names from table 'categories' with :
SELECT col.column_name
FROM information_schema.columns AS col
WHERE col.table_schema= database() and col.table_name = "categories"
This produces a resultset like this :
column_name
-----------------
categoryID
name
description
I can retrieve all values for a specific category with :
SELECT *
FROM categories AS c
WHERE c.categoryID=12345
this results in a resultset like this:
categoryID | name | description
------------------------------------------------
12345 | test | this is a test
Now I would like to get some kind of join of above selects to get a resultset that looks something like this :
fieldname | value
----------------------------------------
categoryID | 12345
name | test
description | this is a test
Does anyone know if this is possible ? Can you do a join on strings that come from another select ??
The reason for this is that I'm writing a universal stored procedure that outputs all fields + their values from a table, without knowing what fields there are in the table. (The tablename is given in a parameter)
