COLUMN NAME and COLUMN COMMENT from one table and COLUMN VALUE from another. How?

Question

I have a table called tbl_mainsheet7 created like this:

pk_mainsheet  client_id  project_id  mainsheet_id  project_cat  EA_WTRESRVD  EA_WTRESRV  EA_FEEASBT
------------  ---------  ----------  ------------  -----------  -----------  ----------  ----------
           1        111         222           333            3            0           0           0
           2         11          22            33            3            0           0           0

MySQL INFORMATION_SCHEMA.COLUMNS Query for tbl_mainsheet7 created like this:

SELECT `COLUMN_NAME`, `COLUMN_COMMENT` FROM INFORMATION_SCHEMA.COLUMNS WHERE `TABLE_NAME` = 'tbl_mainsheet7'

..returning this:

COLUMN_NAME    COLUMN_COMMENT                                        
-------------  ------------------------------------------------------
pk_mainsheet                                                         
client_id                                                            
project_id                                                           
mainsheet_id                                                         
project_cat                                                          
EA_WTRESRVD    EMERGENCY SERVICE CALL                                
EA_WTRESRV     EMERGENCY SERVICE CALL AFTER HRS                      
EA_FEEASBT     ASBESTOS TEST FEE

How can I...

SELECT COLUMN_NAME, COLUMN_VALUE, COLUMN_COMMENT FROM ... WHERE...

Maybe a JOIN? I am really scratching my head.

UPDATE

So I got this to work but for a single predetermined column only. How can I use a variable to make this dynamic?

Like replacing WTRESRVD with a variable relating to COLUMN_NAME

SELECT COLUMN_NAME, (SELECT EA_WTRESRVD FROM tbl_mainsheet7 WHERE client_id = '111') AS COLUMN_VALUE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_mainsheet7'

directly in SQL you can't becasue you can't use column name as variable .. — ScaisEdge, Aug 06 '17 at 15:29
yes .. is a bit complex but you can generate the mysql code string using the vars from php and concatenate the values properly .. try a search in SO for build dinamic sql — ScaisEdge, Aug 06 '17 at 16:43
Wait! What if we used a SUBQUERY? SELECT `COLUMN_NAME`, `COLUMN_COMMENT`, `SUBQUERY HERE BASED ON PARENT COLUMN_NAME` FROM... — suchislife, Aug 06 '17 at 17:06
Have a look on [this post](https://stackoverflow.com/a/16359654/7564182), there is some magic code to turn columns into rows. OR rows into colums [here](https://stackoverflow.com/a/12005676/7564182). — Myonara, Aug 06 '17 at 17:25
Alright guys. Check out my updated working but hard coded version — suchislife, Aug 06 '17 at 17:31

score 0 · Answer 1 · answered Aug 07 '17 at 16:08

I've adapted the solution from this answer for your needs with some changes: 1) I put the pk_mainsheet as an identifier of rows in the target tables 2) I discovered a length issue with the @sql variable, there seems to be a limitation in the result, when you need more then the columns as in table tbl_mainsheet7 now. Hope that helps.

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select pk_mainsheet, ''',
      c.column_name,
      ''' as COLUMN_NAME, ',
      c.column_name,
      ' as COLUMN_VALUE, ''',
      c.column_comment, 
      ''' as COLUMN_COMMENT from tbl_mainsheet7'
    ) SEPARATOR ' UNION ALL 
    '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'tbl_mainsheet7'
and  c.column_name <> 'pk_mainsheet'
order by c.ordinal_position;
-- INTO @sql

SET @sql 
  = CONCAT('select COLUMN_NAME,COLUMN_VALUE,COLUMN_COMMENT
           from
           (', @sql, ') x WHERE Pk_mainsheet = 1 ');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COLUMN NAME and COLUMN COMMENT from one table and COLUMN VALUE from another. How?

1 Answers1