How to Transpose User Email Addresses into Columns Showing Only the Three Newest?

Transpose user email addresses into columns to show latest records

Suppose you have a table listing multiple email addresses for each user. The goal is to transpose these emails into columns that show the "most recent" three addresses based on their creation date. This will produce an output table that looks like this:

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

To achieve this transposition, we can use the crosstab() function in the tablefunc module:

<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
   $$,
   $$SELECT generate_series(1,3)$$
) AS ct (user_id int, user_name text, email1 text, email2 text, email3 text);</code>

The

external SELECT statement extracts user-specific data, limiting the results to the three most recent email addresses based on the row_number() function. The crosstab() function then transposes these emails into columns, ensuring that missing values ​​are represented as NULL.

The above is the detailed content of How to Transpose User Email Addresses into Columns Showing Only the Three Newest?. For more information, please follow other related articles on the PHP Chinese website!