Транспонирование динамических столбцов в строки

Я хотел бы знать, как unpivot Table_1 в 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

Итак, как транспонировать столбцы в Table_1, приводящие к Expected_Result_Table, учитывая только значения > 0?

2 9
mysql unpivot

2 ответа:

В MySQL нет функции UNPIVOT, но вы можете преобразовать ваши столбцы в строки с помощью UNION ALL.

Основной синтаксис: 

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;

В вашем случае вы заявляете, что вам нужно решение для динамических столбцов. Если это так, то вам нужно будет использовать подготовленный оператор для создания динамического 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;

Смотрите SQL Fiddle with Demo

20

Вы в основном распаковываете данные из столбцов в строки, для чего вы можете использовать UNION ALL. Фильтры могут быть применены к непривитым подзапросам или по отдельности к деталям.

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;
6