CODEWITHSUNDEEP
postgresql
edswartz
edswartz

Reputation: 491

use strings in PostgreSQL as variables to find column names

I have two tables where I want to join them and do a look up from one to find the column heading in another.

One table looks like this:

table: student_score            
student|    red |blue   |green
------- -------- ------- -----
201    |    88  |89     |78
345    |    67  |72     |95
987    |    75  |81     |89

The other is like this:

table: student_history  
student |   color_last_year
-------  -----------------
201     |    red
345     |    blue
987     |    green

I'm looking to create a query in PostgreSQL that will allow me to pick last year's color (from the history table) as the column heading from the score table. In the past I've used javascript to do this, but would prefer to do it all in one psql query.

The js looked something like this:

function lastYear(color){
 var query = 'SELECT student_score.' + color + ' 
  FROM student_score 
  JOIN student_score ON student_score.student = 
   student_history.student 
    //...more code .... //;' 
 }

I've been trying to find help around this in documentation and searches, but not sure how best to set up my query.

Upvotes: 0

Views: 83

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use a case expression:

select
    s.student,
    case h.color_last_year
        when 'red' then s.red
        when 'blue' then s.blue
        when 'green' then s.green
    end as val
from student_score s
join student_history h on s.student = h.student;

Upvotes: 3

Related Questions