MySQL : select/join on strings as fieldnames - is this possible?

Posted by Dylan on Stack Overflow See other posts from Stack Overflow or by Dylan
Published on 2011-01-03T17:50:58Z Indexed on 2011/01/03 17:53 UTC
Read the original article Hit count: 193

Filed under:
|
|

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)

© Stack Overflow or respective owner

Related posts about mysql

Related posts about select