Transposing Dynamic Columns to Rows

问题:

I'd like to know how to unpivot Table_1 into Expected_Result_Table:

Table1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

So, How to transpose columns in Table_1 resulting in Expected_Result_Table, considering only values > 0?

回答1:

MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL.

The basic syntax is:

select id, word, qty
from
(
  select id, 'abc' word, abc qty
  from yt
  where abc > 0
  union all
  select id, 'brt', brt
  from yt
  where brt > 0
) d
order by id;

In your case, you state that you need a solution for dynamic columns. If that is the case, then you will need to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


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

See SQL Fiddle with Demo

回答2:

You are basically unpivoting the data from columns into rows, for which you can use UNION ALL. The filters can be applied across the unpivoted subquery, or individually to the parts.

select id, Word, Qty from
(
  select id, 'abc' Word, abc Qty from table1 
  union all
  select id, 'brt', brt from table1
  union all
  select id, 'ccc', ccc from table1
  union all
  select id, 'ddq', ddq from table1
  union all
  select id, 'eee', eee from table1
  union all
  select id, 'fff', fff from table1
  union all
  select id, 'gga', gga from table1
  union all
  select id, 'hxx', hxx from table1
) x
where Qty > 0
order by id;