Solved

SQL Crosstab (Using PostgreSQL)

Posted on 2012-03-19
4
896 Views
Last Modified: 2012-03-22
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
Comment
Question by:DannyJOsborne
  • 2
4 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37744216
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
 
LVL 22

Expert Comment

by:earth man2
ID: 37745192
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 37746201
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
 

Author Closing Comment

by:DannyJOsborne
ID: 37751677
Thanks Earthman2, this appears to work!

Danny
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question