By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t (
response int,
document int,
label text,
value text
);
INSERT INTO t VALUES
(71788176, 79907201, 'bill', '26899'),
(71788176, 79907201, 'amount', '1'),
(71788176, 79907201, 'product', 'shoes'),
(71788176, 79907201, 'price', '25.99');
SELECT * FROM t
4 rows affected
| response | document | label | value |
|---|---|---|---|
| 71788176 | 79907201 | bill | 26899 |
| 71788176 | 79907201 | amount | 1 |
| 71788176 | 79907201 | product | shoes |
| 71788176 | 79907201 | price | 25.99 |
SELECT
response,
document,
MAX(value) FILTER (WHERE label = 'bill') as bill,
MAX(value) FILTER (WHERE label = 'amount') as amount,
MAX(value) FILTER (WHERE label = 'product') as product,
MAX(value) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document
| response | document | bill | amount | product | price |
|---|---|---|---|---|---|
| 71788176 | 79907201 | 26899 | 1 | shoes | 25.99 |