How to use Horizontal data saved in a table as Columns in postgres

Question

I have a table like this:

Table VR

ID  Key         Value   Date
123 First_Name  Vlad    01-01-2011
234 First_Name  Jim     07-01-2011
456 First_Name  Olimpia 10-02-2012
123 Last_Name   Kofman  12-02-2014
234 Last_Name   Cramer  14-02-2014
456 Last_Name   Dukakis 04-03-2015
123 Dept    IT          08-03-2016
234 Dept    Marketing
456 Dept    Accounting
123 Sex M
234 Sex M
456 Sex F

Now, I want to write a query to get the Value where First_Name ='Vlad' AND Last_Name='Cramer'. Another Scenarios is I want to fetch all the records between the date '01-01-2011' to '14-02-2016'.

The query I wrote is:

SELECT VR.key,
      Min(( CASE
              WHEN ( VR.key = 'FIRST_NAME' ) THEN
             VR.value
              ELSE 'n/a'
            END )) AS first_name,
      Min(( CASE
              When ( VR.key = 'LAST_NAME' ) THEN
              VR.value
              ELSE 'n/a'
            END )) AS last_name
FROM   VR
WHERE   ( ( VR.first_name = 'Vlad' )
          AND ( VR.last_name = 'Cramer' ) )
GROUP  BY VR.key
ORDER  BY VR.first_name,
VR.last_name

The exception I am getting is Amazon](500310) Invalid operation: column VR.first_name does not exist;

Can anyone please help me getting how should I achieve the desired result.

Answer 1

Your table does not have a columns called first_name or last_name - these are values in the key column.

I think that you are actually looking for conditional aggregation:

select id
from vr
where key in ('First_Name', 'Last_Name')
group by id
having 
    max(case when key = 'First_Name' and value = 'Vlad' then 1 end) = 1
    and max(case when key = 'Last_Name' and value = 'Cramer' then 1 end) = 1

This gives you ids that have their first name equal to Vlad and last name equal to Cramer.