add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 Subject (
id serial primary key,
name varchar(30) not null
);

insert into Subject (id, name) values
(1 ,'fruit')
,(2 ,'drink')
,(3 ,'vege')
,(4 ,'fish');
create table Journal (
id int,
subj int,
reference varchar(30),
value int
);

insert into Journal
(id, subj, reference, value) values
(1, 1, 'foo', 30)
,(2, 2, 'bar', 20)
,(3, 1, 'bar', 35)
,(4, 1, 'bar' ,10)
,(5, 2, 'baz', 25)
,(6, 4, 'foo', 30)
,(7, 4, 'bar', 40)
,(8, 1, 'baz', 20)
,(9, 2, 'bar', 5);
CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 9
SELECT $f$SELECT * FROM crosstab(
$$SELECT DISTINCT ON (1, 2)
j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j.value) AS val
FROM Journal j
JOIN Subject s ON s.id = j.subj
GROUP BY j.reference, j.subj, s.name
ORDER BY j.reference$$

,$$VALUES ($f$ || string_agg(quote_literal(data_type), '), (') || $f$)$$)
AS x (reference text, $f$ || string_agg(quote_ident(data_type), ' int, ') || ' int)'
AS Stmt
FROM (SELECT concat('subj_', id, '_', name) AS data_type FROM Subject) x

stmt
SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
       j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j.value) AS val
       FROM Journal j
       JOIN Subject s ON s.id = j.subj
       GROUP BY j.reference, j.subj, s.name
       ORDER BY j.reference$$

    ,$$VALUES ('subj_1_fruit'), ('subj_2_drink'), ('subj_3_vege'), ('subj_4_fish')$$)
AS x (reference text, subj_1_fruit int, subj_2_drink int, subj_3_vege int, subj_4_fish int)		 
SELECT 1
SELECT * FROM crosstab(
$$SELECT DISTINCT ON (1, 2)
j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j.value) AS val
FROM Journal j
JOIN Subject s ON s.id = j.subj
GROUP BY j.reference, j.subj, s.name
ORDER BY j.reference$$

,$$VALUES ('subj_1_fruit'), ('subj_2_drink'), ('subj_3_vege'), ('subj_4_fish')$$)
AS x (reference text, subj_1_fruit int, subj_2_drink int, subj_3_vege int, subj_4_fish int)

reference subj_1_fruit subj_2_drink subj_3_vege subj_4_fish
bar 45 25 null 40
baz 20 25 null null
foo 30 null null 30 
SELECT 3