Transposition de colonnes dynamiques en lignes

Je voudrais savoir comment unpivot Table_1 en 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

Donc, Comment faire pour transposer les colonnes dans Table_1 résultant dans Expected_Result_Tableen considérant seulement les valeurs > 0?

source d'informationauteur Romualdo Alves

  1. 20

    MySQL n'a pas de UNPIVOT fonction, mais vous pouvez convertir vos colonnes en lignes à l'aide d'un UNION ALL.

    La syntaxe de base est:

    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;

    Dans votre cas, vous devez indiquer que vous avez besoin d'une solution pour les colonnes dynamiques. Si c'est le cas, alors vous aurez besoin d'utiliser une requête préparée pour générer le SQL dynamique:

    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;

    Voir SQL jouer avec la Démo

  2. 6

    Vous êtes essentiellement unpivoting les données des colonnes en lignes, pour lequel vous pouvez utiliser de l'UNION de TOUS. Les filtres peuvent être appliqués à l'ensemble des unpivoted sous-requête, ou individuellement pour les pièces.

    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;