sqlpostgresqljoinduplicates

SQL join - transform row into column


I'm trying to execute a SQL query to transform some rows into columns. The problem: I have more rows at the end.

Example: I have only one table in Postgres.

id account category sub_category jsondata (JSONB column) date
1 1 0 1 {"value1":"test","value2":"test"} 01/05/2025 08:00:00
2 1 0 0 {"location":"A","Phone":"B"} 01/05/2025 09:00:00
3 1 0 1 {"value1":"test","value2":"test"} 01/05/2025 09:05:00
4 1 0 1 {"value1":"test","value2":"test"} 03/05/2025 09:06:00
5 1 0 0 {"location":"B","Phone":"C"} 04/05/2025 10:00:00
6 1 0 1 {"value1":"test","value2":"test"} 04/05/2025 10:15:00
7 2 0 1 {"value1":"test","value2":"test"} 03/05/2025 09:30:00

I'm trying to get this result:

id Account category sub_category jsondata (JSONB column) date location phone
1 1 0 1 {"value1":"test","value2":"test"} 01/05/2025 08:00:00 null null
2 1 0 0 {"location":"A","Phone":"B"} 01/05/2025 09:00:00 A B
3 1 0 1 {"value1":"test","value2":"test"} 01/05/2025 09:05:00 A B
4 1 0 1 {"value1":"test","value2":"test"} 03/05/2025 09:06:00 A B
5 1 0 0 {"location":"B","Phone":"C"} 04/05/2025 10:00:00 B C
6 1 0 1 {"value1":"test","value2":"test"} 04/05/2025 10:15:00 B C
7 2 0 1 {"value1":"test","value2":"test"} 03/05/2025 09:30:00 null null

Here is my query:

SELECT
    A.id,
    A.account,
    A.category,
    A.sub_category,
    A.date,
    A.jsondata,
    B.jsondata ->> 'location' AS "location",
    B.jsondata ->> 'Phone' AS "Phone",
FROM
    table1 A
LEFT JOIN 
    table1 AS B ON B.category = 0
                AND B.sub_category = 0
                AND B.account = A.account
                AND B.date = (SELECT date
                              FROM table1 AS C
                              WHERE C.category = 0
                                AND C.sub_category = 0
                                AND C.account = A.account
                                AND C.date <= A.date
                              ORDER BY
                                C.date DESC
                              LIMIT 1)
ORDER BY
    account, date;

Thanks for your help


Solution

  • I tested the script below in PostgreSQL, and it worked. I hope this helps.

    SELECT
      A.id,
      A.account,
      A.category,
      A.sub_category,
      A.date,
      A.jsondata,
      loc_data.location,
      loc_data.phone
    FROM
      table1 A
    LEFT JOIN LATERAL (
      SELECT
        B.jsondata ->> 'location' AS location,
        B.jsondata ->> 'Phone' AS phone
      FROM
        table1 B
      WHERE
        B.account = A.account
        AND B.category = 0
        AND B.sub_category = 0
        AND B.date <= A.date
      ORDER BY
        B.date DESC
      LIMIT 1
    ) loc_data ON true
    ORDER BY
      A.account,
      A.date;