How to Transpose the Three Most Recent User Emails into Columns in PostgreSQL?

Using crosstab() to Pivot Recent User Emails in PostgreSQL

Challenge: You need to transform a table with multiple user emails into a format where the three most recent emails for each user are displayed as separate columns.

Solution: PostgreSQL's crosstab() function (from the tablefunc extension) provides an efficient solution. This approach involves these key steps:

<code class="language-sql">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);</code>

Breakdown:

1. Subquery: A subquery ranks emails for each user based on creation_date using ROW_NUMBER(). NULLS LAST ensures that NULL creation dates are handled appropriately.

2. Filtering: The WHERE rn < 4 clause restricts the results to the three most recent emails per user.

3. crosstab() Function: The crosstab() function pivots the ranked email data. The VALUES (1),(2),(3) parameter defines the three output columns (email1, email2, email3).

4. Output: The final query returns a table with user_id, user_name, and the three most recent email addresses as separate columns.

This method efficiently handles the transposition, providing a clear and concise solution for extracting the most recent email data. Remember to enable the tablefunc extension if it's not already enabled in your PostgreSQL database.

The above is the detailed content of How to Transpose the Three Most Recent User Emails into Columns in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!