Transpose Latest Rows Per User to Columns

Transpose latest rows per user to columns

Use crosstab() from the tablefunc module.

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);

I used dollar-quoting for the first parameter, which has no special meaning. It's just convenient to escape single quotes in the query string, which is a common case:

• Insert text with single quotes in PostgreSQL

Detailed explanation and instructions:

• PostgreSQL Crosstab Query

And in particular, for "extra columns":

• Pivot on Multiple Columns using Tablefunc

The special difficulties here are:

• The lack of key names.
  
  → We substitute with row_number() in a subquery.

• The varying number of emails.
  
  → We limit to a max. of three in the outer SELECT and use crosstab() with two parameters, providing a list of possible keys.

Pay attention to NULLS LAST in the ORDER BY.

Postgres Transpose Rows to Columns Based on Column Value

Assuming you have a fixed 4 quarters per year which you want to display, use pivoting logic:

SELECT
    stock,
    year,
    MAX(amount) FILTER (WHERE statement = 'Q1 Earnings') AS "Q1 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q2 Earnings') AS "Q2 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q3 Earnings') AS "Q3 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q4 Earnings') AS "Q4 Earnings"
FROM statement
GROUP BY
    stock,
    year;

PostgreSQL simply transpose/convert rows to columns

You could use a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT value, ROW_NUMBER() OVER (ORDER BY value) rn
    FROM yourTable
)

SELECT
    MAX(value) FILTER (WHERE rn = 1) AS tmp,
    MAX(value) FILTER (WHERE rn = 2) AS tmp02,
    MAX(value) FILTER (WHERE rn = 3) AS tmp03
FROM cte;

Demo

Transpose Rows to Columns Dynamically in MySQL - Page Rank Per User

You can use conditional aggregation:

select
    id,
    name,
    max(case when rank = 1 then page     end) page1,
    max(case when rank = 1 then quantity end) quantity1,
    max(case when rank = 2 then page     end) page2,
    max(case when rank = 2 then quantity end) quantity2,
    max(case when rank = 3 then page     end) page3,
    max(case when rank = 3 then quantity end) quantity3,
    max(case when rank = 4 then page     end) page4,
    max(case when rank = 4 then quantity end) quantity4
from sessions
group by id, name

Demo on DB Fiddle:

id | name | page1  | quantity1 | page2    | quantity2 | page3 | quantity3 | page4    | quantity4
-: | :--- | :----- | --------: | :------- | --------: | :---- | --------: | :------- | --------:
 1 | joan | home   |        15 | search   |         8 | vip   |         4 | checkout |         2
 2 | fill | home   |        20 | vip      |         2 | null  |      null | null     |      null
 3 | mery | search |        10 | checkout |         7 | home  |         5 | null     |      null

---

Related Topics