troubleshooting Question

SQL Crosstab (Using PostgreSQL)

Avatar of Danny Osborne
Danny Osborne asked on
PostgreSQLSQL
4 Comments1 Solution1043 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros