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