How to Transpose Row Data of User Emails into Columns in PostgreSQL?

Convert row data into columns: display the user’s latest email address

Question:

You have multiple email addresses per user in your table, and you want to flatten it into columns so that it displays the three most recent email addresses for each user based on creation date.

Expected output:

user_name	user_id	email1	email2	email3
Mary	123	[email protected]	[email protected]	[email protected]
Joe	345	[email protected]	NULL	NULL

Solution:

To do this, you can take advantage of the crosstab() function of the tablefunc module in PostgreSQL:

<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 <= 3
    ORDER BY user_id, rn
    $$,
    'VALUES (1),(2),(3)'
) AS ct (user_id integer, user_name text, email1 text, email2 text, email3 text);</code>

Explanation:

• The subquery retrieves each user's user ID, username, and email address, as well as their ordinal numbers. The row_number() function is used to determine the mailbox order for each user, where NULLS LAST ensures that NULL values ​​are considered the latest values.
• The crosstab() function then transposes the mailbox into columns using row numbers as keys. The second parameter "VALUES (1),(2),(3)" of crosstab() specifies the three columns to be created.
• The final SELECT statement returns the transposed data, where each user's three most recent mailboxes appear in the email1, email2, and email3 columns.

Note:

• If each user has a different number of mailboxes, you can replace "VALUES (1),(2),(3)" with a list of all possible key values.
• Note the use of NULLS LAST in the ORDER BY clause to ensure that NULL values ​​are treated as the latest values.

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