How to Transpose User Email History Rows into Columns in PostgreSQL?

Restructuring User Email History: Rows to Columns in PostgreSQL

Imagine a table storing multiple email addresses for each user. This article demonstrates how to efficiently transform this row-based data into a columnar format, specifically aligning the most recent email addresses as distinct columns. We'll achieve this using PostgreSQL's tablefunc module and the powerful crosstab() function.

The crosstab() Solution

The crosstab() function within the tablefunc module is perfectly suited for transposing rows into columns on a per-user basis. The following query illustrates its application:

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
   $$,
   $$VALUES (1),(2),(3)$$
) AS ct (user_id INT, user_name TEXT, email_1 TEXT, email_2 TEXT, email_3 TEXT);

Understanding the Query Components

• The first argument to crosstab() is a subquery. This subquery retrieves user IDs, usernames, a row number (rn), and email addresses.
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) assigns a unique rank (rn) to each email address for each user, ordered by creation date (most recent first).
• The WHERE clause (WHERE rn ) limits the results to the three most recent email addresses per user.
• The second argument to crosstab() ($$VALUES (1),(2),(3)$$) specifies the potential values for the new columns (representing the email address ranks).

Addressing Potential Challenges

The original table's lack of a distinct key for each email address necessitated the use of row_number() to generate a surrogate key. Furthermore, the variable number of emails per user required limiting the output to three columns in the outer SELECT statement and defining the range of possible key values in the second crosstab() parameter.

This approach effectively transforms the latest email addresses per user into separate columns, presenting a cleaner and more organized view of user email history.

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