• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

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
0
Danny Osborne
Asked:
Danny Osborne
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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;
0
 
earth man2Commented:
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 );
0
 
earth man2Commented:
select e.sample_id,
(select max( final_value ) from sample_results a where a.sample_id = e.sample_id and characteristic_id=1 and created_at >  date '01-01-2012' group by a.sample_id )as sulphur,
(select max( final_value ) from sample_results b where b.sample_id = e.sample_id and characteristic_id=2 and created_at >  date '01-01-2012' group by b.sample_id )as ash,
(select max( final_value ) from sample_results c where c.sample_id = e.sample_id and characteristic_id=3 and created_at >  date '01-01-2012' group by c.sample_id )as  calcium,
(select max( final_value ) from sample_results d where d.sample_id = e.sample_id and characteristic_id=4 and created_at >  date '01-01-2012' group by d.sample_id )as iron
from ( select distinct sample_id from sample_results  where created_at >  date '01-01-2012' )as e;
0
 
Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor Commented:
Thanks Earthman2, this appears to work!

Danny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now