Combinez plusieurs lignes dans une ligne MySQL

Dire que j'ai deux tables dans une Base de données MySQL.

Tableau 1:

ID    Name
1     Jim
2     Bob
3     John

Tableau 2:

ID    key           value
1     address       "X Street"
1     city          "NY"
1     region        "NY"
1     country       "USA"
1     postal_code   ""
1     phone         "123456789"

Lors de la sélection des lignes à partir de la base de données, est-il possible de rejoindre les lignes de la seconde table comme des colonnes sur la première table?

Le résultat souhaité à droite de la requête MySQL est:

ID    Name    address    city    region   country   postal_code   phone
1     Jim     X Street   NY      NY       USA       NULL          123456789
2     Bob     NULL       NULL    NULL     NULL      NULL          NULL
3     John    NULL       NULL    NULL     NULL      NULL          NULL

Merci pour toute aide!

source d'informationauteur n0nnus

  1. 25

    Ce type de transformation de données est connu comme un PIVOT. MySQL n'a pas une fonction de pivot, mais vous pouvez le reproduire à l'aide d'une fonction d'agrégation avec un CASE expression:

    select t1.id,
  t1.name,
  max(case when t2.`key` = 'address' then t2.value end) address,
  max(case when t2.`key` = 'city' then t2.value end) city,
  max(case when t2.`key` = 'region' then t2.value end) region,
  max(case when t2.`key` = 'country' then t2.value end) country,
  max(case when t2.`key` = 'postal_code' then t2.value end) postal_code,
  max(case when t2.`key` = 'phone' then t2.value end) phone
from table1 t1
left join table2 t2
  on t1.id = t2.id
group by t1.id, t1.name

    Voir SQL jouer avec la Démo.

    Cela pourrait aussi être écrite à l'aide de plusieurs jointures sur votre table2 et vous devez inclure un filtre sur la jointure pour chaque key:

    select t1.id,
  t1.name,
  t2a.value address,
  t2c.value city,
  t2r.value region,
  t2y.value country,
  t2pc.value postal_code,
  t2p.value phone
from table1 t1
left join table2 t2a
  on t1.id = t2a.id
  and t2a.`key` = 'address'
left join table2 t2c
  on t1.id = t2c.id
  and t2c.`key` = 'city' 
left join table2 t2r
  on t1.id = t2r.id
  and t2c.`key` = 'region' 
left join table2 t2y
  on t1.id = t2y.id
  and t2c.`key` = 'country' 
left join table2 t2pc
  on t1.id = t2pc.id
  and t2pc.`key` = 'postal_code' 
left join table2 t2p
  on t1.id = t2p.id
  and t2p.`key` = 'phone';

    Voir SQL jouer avec la Démo.

    Les deux versions sera excellent travail si vous avez un nombre limité de key valeurs. Si vous avez un nombre inconnu de valeurs, alors vous aurez envie de regarder à l'aide d'une requête préparée pour générer le SQL dynamique:

    SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t2.`key` = ''',
      `key`,
      ''' then t2.value end) AS `',
      `key`, '`'
    )
  ) INTO @sql
from Table2;

SET @sql 
    = CONCAT('SELECT t1.id, t1.name, ', @sql, ' 
              from table1 t1
              left join table2 t2
                on t1.id = t2.id
              group by t1.id, t1.name;');

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

    Voir SQL jouer avec la Démo

    Toutes les versions donne un résultat:

    | ID | NAME |  ADDRESS |   CITY | REGION | COUNTRY | POSTAL_CODE |     PHONE |
|---- | ------ | ---------- | -------- | -------- | --------- | ------------- | -----------|
|  1 |  Jim | X Street |     NY | (null) |  (null) |      (null) | 123456789 |
|  2 |  Bob |   (null) | (null) | (null) |  (null) |      (null) |    (null) |
|  3 | John |   (null) | (null) | (null) |  (null) |      (null) |    (null) |
  2. 2

    Vous avez une structure appelée entité-attribut-valeur dans la seconde table. Il y a deux façons de faire la combinaison. Je pense que la méthode d'agrégation est la plus simple à exprimer:

    select t1.name,
       max(case when `key` = 'address' then value end) as address,
       max(case when `key` = 'city' then value end) as city,
       max(case when `key` = 'region' then value end) as region,
       max(case when `key` = 'country' then value end) as country,
       max(case when `key` = 'postal_code' then value end) as postal_code,
       max(case when `key` = 'phone' then value end) as phone
from table1 t1 left join
     table2 t2
     on t1.id = t2.id
group by t1.name;

    La seconde méthode consiste à faire des jointures pour chaque valeur:

    select t1.name, address.value, city.value, . . .
from table1 t1 left join
     table2 address
     on t1.id = address.id and address.`key` = 'Address' left join
     table2 city
     on t1.id = city.id and city.`key` = 'City' . . .

    En fonction de la structure des données, la join méthode peut effectivement être plus rapide dans MySQL, quand il utilise une indexation appropriée. (D'autres bases de données ont été algorithmes d'agrégation, de sorte que le group by méthode fonctionne bien dans d'autres bases de données.)