sql - (Postgres) How to set value based on row's value

Question

For example, I have this table:

Table_A

  id  | classification |  key       |    value     | date_time
------+----------------+------------+--------------+--------------------
   1  | Num_A          | Odd        | 7            | 01-11-2021 01:00
   2  | Num_A          | Status     | Valid        | 01-11-2021 01:00
   3  | Num_A          | Odd        | 9            | 01-11-2021 02:00
   4  | Num_A          | Status     | Valid        | 01-11-2021 02:00
   5  | Num_B          | Odd        | 11           | 01-11-2021 02:00
   6  | Num_B          | Status     | Invalid      | 01-11-2021 02:00
   7  | Num_C          | Even       | 10           | 01-11-2021 03:00
   8  | Num_C          | Status     | Valid        | 01-11-2021 03:00

My goal is to create a query that will result like this:

  id  | classification |  key       |    value     | date_time
------+----------------+------------+--------------+--------------------
   1  | Num_A          | Odd        | Valid        | 01-11-2021 01:00
   3  | Num_A          | Odd        | Valid        | 01-11-2021 02:00
   5  | Num_B          | Odd        |              | 01-11-2021 02:00
   7  | Num_C          | Even       | Valid        | 01-11-2021 03:00

Basically, this is just mapping of the key and value. If value is 'Invalid', display as blank (can be achieved using CASE statements).

I have tried the several SQLs however I still cannot achieve the desired output. How can I achieve this?

By the way, I am using PostgreSQL 11.

Thank you in advance.

question from:https://stackoverflow.com/questions/65662022/postgres-how-to-set-value-based-on-rows-value

Answer 1

There are lots of way to achieve this, but I'll use inner join because this will work in most DBMS.

SELECT a.id,a.classification,a.key, 
CASE b.value WHEN 'valid' THEN 'valid' ELSE ' ' END as value,
a.date_time 
from table_A a 
INNER JOIN table_A b ON a.classification = b.classification 
AND a.date_time = b.date_time AND b.key = 'Status'
WHERE a.keys <> 'Status'

but I think you have to make sure there is no duplicate classification with same date_time with this query.

---

Aside from the problem itself, OP should normalize your data, this "problem" will not even exist if the data have been proper normalize.

FOR EXAMPLE:

id	class	key	value	date	valid
1	NUM_A	ODD	7	01-11-2021 01:00	valid