Link to home
Create AccountLog in
Avatar of Danny Osborne
Danny Osborne

asked on

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
Avatar of lcohan
lcohan
Flag of Canada image

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;
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
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Danny Osborne
Danny Osborne

ASKER

Thanks Earthman2, this appears to work!

Danny