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;