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
Danny OsborneSenior IT and Technical Specialist, ConsultancyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor Commented:
Thanks Earthman2, this appears to work!

Danny
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.