CODEWITHSUNDEEP
sql
AKG
AKG

Reputation: 13

SQL Partition by case statement and flag

I have run into an issue,

the table is partitioned by col2 by values, BB and CC, and the have their respective status in col3, what i want is to apply a flag for every row in col2, e.g if any or all rows in col2 has status 'ST_Inprogress' or 'ST_Approved' then flag for all the rows in col2 should be 'Y' else it should be 'N' like given table in the attached picture

enter image description here

Could someone please help.

Upvotes: 0

Views: 1827

Answers (1)

forpas
forpas

Reputation: 164069

With MAX() window function:

select *,
  max(case when Col3 in ('ST_Inprogress', 'ST_Approved') then 'Y' else 'N' end) over (partition by Col2) flag
from tablename

See the demo for Postgresql and for SQL Server.
Results:

| Col1 | Col2 | Col3        | flag |
| ---- | ---- | ----------- | ---- |
| AA   | BB   | ST_Closed   | N    |
| AA   | BB   | ST_Closed   | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Closed   | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | BB   | ST_Deleted  | N    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Approved | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Deleted  | Y    |
| AA   | CC   | ST_Approved | Y    |

Upvotes: 1

Related Questions