tabla - referencias cruzadas postgresql

Postgres-Transposición de filas a columnas

Question

Tengo la siguiente tabla, que da múltiples direcciones de correo electrónico para cada usuario.

Necesito aplanar esto en columnas en una consulta de usuario. Para darme las "3 últimas" direcciones de correo electrónico basadas en la fecha de creación.

user.name | user.id | email1 | email2 | email3**
Mary | 123 | [email protected] | [email protected] | [email protected]
Joe | 345 | [email protected] | [NULL] | [NULL]

Answer 1

Si alguien más encuentra esta pregunta y necesita una solución dinámica para esto, donde tiene una cantidad indefinida de columnas para transponer y no exactamente 3, puede encontrar una buena solución aquí: https://github.com/jumpstarter-io/colpivot

Answer 2

Use la crosstab() de tablefunc crosstab() del módulo 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);

Usé cotizaciones en dólares para el primer parámetro, que no tiene un significado especial. Es conveniente si debe evitar comillas simples en la cadena de consulta, que es un caso común:

• Insertar texto con comillas simples en PostgreSQL

Explicación detallada e instrucciones aquí:

• Consulta de tabla cruzada de PostgreSQL

Y en particular, para "columnas adicionales":

• Pivote en múltiples columnas usando Tablefunc

Las dificultades especiales aquí son:

• La falta de nombres clave.
  -> Sustituimos con row_number() en una subconsulta.

• La cantidad variable de correos electrónicos.
  -> Limitamos a un máximo. de tres en el exterior SELECT
  y use la crosstab() con dos parámetros, proporcionando una lista de claves posibles.

Preste atención a NULLS LAST en el ORDER BY .