CODEWITHSUNDEEP
sqlgroup-byamazon-redshiftentity-attribute-value
CoderBeginner
CoderBeginner

Reputation: 837

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

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.

Upvotes: 0

Views: 807

Answers (2)

GMB
GMB

Reputation: 222672

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.

Upvotes: 1

nbk
nbk

Reputation: 49410

You should build from your VR Table a regular table and use that as starting point to make your query or build a view from that inner SELECT

SELECT 
    *
FROM
    (SELECT 
        VR.id,
            MAX(IF(VR.key = 'First_Name', VR.Value, NULL)) 'First_Name',
            MAX(IF(VR.key = 'Last_Name', VR.Value, NULL)) 'Last_Name',
            MAX(IF(VR.key = 'Dept', VR.Value, NULL)) 'Dept',
            MAX(IF(VR.key = 'Sex M', 'male', IF(VR.key = 'Sex F', 'female', NULL))) 'Gender'
    FROM
        VR
    GROUP BY VR.id) comp
WHERE
    First_Name = 'Vlad'
        AND Last_Name = 'Kofman';

 id | First_Name | Last_Name | Dept | Gender
--: | :--------- | :-------- | :--- | :-----
123 | Vlad       | Kofman    | IT   | male

db<>fiddle here

Upvotes: 1

Related Questions