Search code examples
sqloracleoracle-sqldeveloper

SQL - Loop through duplicates to make new conditional column

Sooooo I have data in Oracle database, from which I take some rows that looks like this

ID  TYPE 
1   A
1   B
2   A

I have a list of Types that translate to conditional column, like (if A then Y, if B then N), so it looks like this

ID  TYPE COND
1   A    Y
1   B    N
2   A    Y

Is there a way to add another column, that would check if all values in COND column for specific ID are Y? So final result would like this:

ID  TYPE COND COND2
1   A    Y    N
1   B    N    N
2   A    Y    Y

I know I could easly do this in R, but skipping using other software in this process would be great.

Solution

  • Use an analytic function. In this case min() does what you want:

    select t.*,
       min(cond) over (partition by id) as cond2
from t;