SQL Crosstab (Using PostgreSQL)

Hi,

Is there an easy way to alter the below SQL so I can view data like this?

sample_id      sulphur      ash        calcium      iron
45954        3.56               13        10
246304               0.0294      5        23
247287               0.0458      6        23
247435               0.0136      7        44
247437               0.0295 12        30
247473        2.47        0.0358 8        14

Rather than like this (Which is what the below sql does)

sample_id      sulphur              ash       calcium      iron
45954        3.56
45954                             13
45954                                        10
246304                         0.0294
246304                                 5
246304                                             23
247287                         0.0458
247287                                 6
247287                                             23
247435                         0.0136
247435                                 7
247435                                             44
247437                         0.0295
247437                                 12
247437                                             30
247473            2.47
247473                         0.0358
247473                                 8
247473                                             14

SELECT
distinct sample_results.sample_id,
--"characteristics"."name",
(select max (sample_results.final_value) where "characteristics"."name" = 'Sulphur Content') as Sulphur,
(select max (sample_results.final_value) where "characteristics"."name" = 'Ash')as Ash,
(select max (sample_results.final_value) where "characteristics"."name" = 'Calcium')as Calcium,
(select max (sample_results.final_value) where "characteristics"."name" = 'Iron')as Iron

FROM
public.sample_results,
public."characteristics"
WHERE
sample_results.characteristic_id = "characteristics".id
and sample_results.created_at > date '01-01-2012'
and (("characteristics"."name" = 'Sulphur Content') or ("characteristics"."name" = 'Ash') or
("characteristics"."name" = 'Calcium') or ("characteristics"."name" = 'Iron'))
group by sample_results.sample_id, "characteristics"."name"
order by sample_results.sample_id;

Danny

PostgreSQLSQL

Last Comment

Danny Osborne

8/22/2022 - Mon

lcohan

3/20/2012

Is this what you want?

SELECT
distinct sample_results.sample_id,
(select max (sample_results.final_value) where characteristics.name = 'Sulphur Content') as Sulphur,
      (select max (sample_results.final_value) where characteristics.name = 'Ash')as Ash,
      (select max (sample_results.final_value) where characteristics.name = 'Calcium')as Calcium,
      (select max (sample_results.final_value) where characteristics.name = 'Iron')as Iron

FROM
public.sample_results,
public.characteristics
WHERE
sample_results.characteristic_id = characteristics.id
and sample_results.created_at > date '01-01-2012'
and ((characteristics.name = 'Sulphur Content') or (characteristics.name = 'Ash') or
(characteristics.name = 'Calcium') or (characteristics.name = 'Iron'))
group by sample_results.sample_id
order by sample_results.sample_id;

earth man2

3/20/2012

execute c:\Program Files\Postgresql\9.0\share\contrib\tablefunc.sql

SELECT * from crosstab(
'select sample_id, characteristic_id, max(final_value) from sample_results
where created_at > date ''01-01-2012''
group by sample_id, characteristic_Id order by 1,2' )
as ct( sample_id int, sulphur_content float, ash float, calcium float, iron float );

ASKER CERTIFIED SOLUTION

earth man2

3/21/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Danny Osborne