(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.

If you had tried, it will be a great idea to also include the query you had tried and it's output (which the closest to what you expect). — T. Peter
– T. Peter, Commented Jan 11, 2021 at 6:16
also you might have to clarify the logic, is it with same classification and date_time suppose to be mapped together? — T. Peter
– T. Peter, Commented Jan 11, 2021 at 6:20

T. Peter · Accepted Answer · 2021-01-11 06:45:47Z

2

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

the data should look like this, instead of storing two different set of data in one single column value.

answered Jan 11, 2021 at 6:45

T. Peter

8856 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

willow Over a year ago

Hello, Peter. Thank you for taking your time to answer. I was able to solve this issue using WITH () SELECT statement ... I will post my solution later for it might also help others in the future. But I marked your answer as helpful. Thanks.

Gordon Linoff · Accepted Answer · 2021-01-11 13:46:54Z

0

You can do this with conditional aggregation:

select classification, date_time,
       max(key) filter (where key in ('Odd', 'Even') ) as key,
       max(value) filter (where key = 'Status' ) as value
from table_a a
group by classification, date_time;

The above returns "Invalid" for the third row, which makes sense to me. However, you can get a NULL value if you really prefer:

select classification, date_time,
       max(key) filter (where key in ('Odd', 'Even') ) as key,
       max(value) filter (where key = 'Status' and status = 'Valid') as value

then value end) as value from table_a a group by classification, date_time;

answered Jan 11, 2021 at 13:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

FOR EXAMPLE:

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

FOR EXAMPLE:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related