Postgres-transposer les lignes en colonnes
j'ai le tableau suivant, qui donne plusieurs adresses email pour chaque utilisateur.
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]
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 parrow_number()
dans une sous-série. -
le nombre variable d'e-mails.
-> Nous limiter à un max. de trois à l'extérieurSELECT
et utilisercrosstab()
avec deux paramètres, en fournissant une liste de clés possibles.
faites attention à NULLS LAST
dans le ORDER BY
.
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