Postgres-transposer les lignes en colonnes

j'ai le tableau suivant, qui donne plusieurs adresses email pour chaque utilisateur.

enter image description here

j'ai besoin d'aplatir cette des colonnes sur une requête de l'utilisateur. Pour me donner les" plus récentes " 3 adresses email basées sur la date de création.

user.name | user.id | email1          | email2           | email3**

Mary      | 123     | mary@gmail.com  | mary@yahoo.co.uk | mary@test.com

Joe       | 345     | joe@gmail.com   | [NULL]           | [NULL]
26
demandé sur Erwin Brandstetter 2014-04-14 16:35:58

2 réponses

utiliser crosstab() du module tablefunc .

SELECT * FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn < 4
     ORDER  BY user_id
   $$
  , 'VALUES (1),(2),(3)'
   ) AS t (user_id int, user_name text, email1 text, email2 text, email3 text);

j'ai utilisé la citation en dollars pour le premier paramètre, qui n'a pas de signification particulière. Il est tout simplement pratique si vous devez échapper des guillemets simples dans la chaîne de requête qui est un cas commun:

explication détaillée et instructions ici:

et en particulier, pour les "colonnes supplémentaires":

Le difficultés particulières voici:

  • L'absence de noms de clé.

    - >Nous remplaçons par row_number() dans une sous-série.

  • le nombre variable d'e-mails.

    -> Nous limiter à un max. de trois à l'extérieur SELECT

    et utiliser crosstab() avec deux paramètres, en fournissant une liste de clés possibles.

faites attention à NULLS LAST dans le ORDER BY .

39
répondu Erwin Brandstetter 2017-05-23 12:02:21

si quelqu'un d'autre qui trouve cette question et a besoin d'une solution dynamique pour cela où vous avez un nombre indéterminé de colonnes à transposer et pas exactement 3, vous pouvez trouver une bonne solution ici: https://github.com/jumpstarter-io/colpivot

8
répondu Hannes Landeholm 2015-10-19 01:16:35