Mysql query to convert two columns to one row (select default values)

Question

I want to select default values from any table.
I want make method for get all default values from any table for using in auto generated form or etc.

So Im created this query:

SELECT `COLUMN_NAME`,`COLUMN_DEFAULT` FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA`='databasename' AND `TABLE_NAME`='tablename' LIMIT 1

The result is simple

| SELECT  | COLUMN_DEFAULT |
+---------+----------------+
| id      | null           |
| name    | 'defname'      |
| content | 'defcontent'   |

I need to convert it to like this

| id   | name    | content   |
+------+---------+-----------+
| null | defname | decontent |

Thanks.

Why are you trying to do this? What is the underlying business problem that you are trying to solve? Sounds like an [XY problem](http://meta.stackexchange.com/a/66378) to me. — eggyal, Feb 11 '14 at 08:14
Is not necessary convert nothing, just use some like this "SELECT id, name, content FROM databasename" or SELECT * .. a simply query. — SoldierCorp, Feb 11 '14 at 08:15
check this [select * transform row value as table column name](http://stackoverflow.com/questions/14765765/select-transform-row-value-as-table-column-name) — Damodaran, Feb 11 '14 at 08:22
I want make method for get all default values from any table for using in auto generated form or etc. — Tito100, Feb 11 '14 at 08:25
So what's wrong with the recordset provided by your existing query? — eggyal, Feb 11 '14 at 08:31
I think the best way is to pivot his table on the application side not using SQL because in this case you need to use a dynamic SQL. — valex, Feb 11 '14 at 08:35
Ok. Now I change it in application side, sou I safe it as is. — Tito100, Feb 11 '14 at 08:37

score 0 · Answer 1 · answered Feb 12 '14 at 08:35

Single straight forward SQL statement can't be achieved here as the number columns for different tables may vary.

I would suggest to define a stored procedure to identify the defined columns of the table in question and generate the pivot table based on those names and values.

Following stored procedure helps you do the same.

delimiter //
drop procedure if exists default_values //

create procedure default_values( in db varchar(255), in tbl varchar(255) )
begin
  declare c_name varchar(255) default '';
  declare all_names_read boolean default false;

  declare c_names cursor for 
    select column_name from information_schema.columns
      where table_schema=db and table_name=tbl;
  declare continue handler for not found 
  -- declare continue handler for SQLSTATE '02000' 
    set all_names_read = true;

  set @sql_query = '\nselect';
  set @rowNumber = 0;

  open c_names;
  repeat
    fetch c_names into c_name;

    set @sql_query = 
          concat( @sql_query, 
                  if( all_names_read, '\n', if( @rowNumber < 1, '\n', ',\n' ) )
                 );

    if( ! all_names_read ) then
      set @rowNumber = @rowNumber + 1;

      set @sql_query = 
            concat( @sql_query, '    max( case column_name when \'',
                    c_name, '\' then column_default end ) as \'',
                    c_name, '\'' );
    end if;

    until all_names_read
  end repeat;

  close c_names;

  set @sql_query =
        concat( @sql_query,
                'from information_schema.columns\n',
                'where table_schema=? and table_name=?' );
  set @db_name = db;
  set @tbl_name = tbl;

  prepare stmt from @sql_query;
  execute stmt using @db_name, @tbl_name;
  -- execute stmt;
  deallocate prepare stmt;
end;
//

delimiter ;

Following is a test on the stored procedure defined.

mysql> create table test.tide
    -> ( t timestamp, i int default 9, d date, e enum( 'yes', 'no' ) default 'no' );

mysql> desc test.tide;
+-------+------------------+------+-----+-------------------+------...
| Field | Type             | Null | Key | Default           | Extra...
+-------+------------------+------+-----+-------------------+------...
| t     | timestamp        | NO   |     | CURRENT_TIMESTAMP | on up...
| i     | int(11)          | YES  |     | 9                 |         
| d     | date             | YES  |     | NULL              |         
| e     | enum('yes','no') | YES  |     | no                |         
+-------+------------------+------+-----+-------------------+------...
4 rows in set (0.02 sec)

mysql> call default_values( 'test', 'tide' );
+-------------------+------+------+------+
| t                 | i    | d    | e    |
+-------------------+------+------+------+
| CURRENT_TIMESTAMP | 9    | NULL | no   |
+-------------------+------+------+------+

Above result was on executing the generated query by stored procedure.
Generated query was this:

mysql> select @sql_query\G
*************************** 1. row ***************************
@sql_query:
select
    max( case column_name when 't' then column_default end ) as 't',
    max( case column_name when 'i' then column_default end ) as 'i',
    max( case column_name when 'd' then column_default end ) as 'd',
    max( case column_name when 'e' then column_default end ) as 'e'
from information_schema.columns
where table_schema=? and table_name=?

Mysql query to convert two columns to one row (select default values)

1 Answers1