Ismail Sensei Ismail Sensei - 1 month ago 8
SQL Question

SQL: How to return specific char to all lines if case expression met true?

Hi i have SQL like that:

SELECT DISTINCT
document,
article,
CASE WHEN isQR='Y' THEN 'Y' ELSE ' ' END AS CONTAINQR
from product


It will output this table:

document | article | CONTAINQR
-----------|-----------|---------
TA1 | AROVAN | Y
TA2 | DOLIPRANE |
TA3 | AMOX |


What i want is: if any article has at least isQR='Y' then it will give all lines 'Y' to alias CONTIANQR.

Answer

Simple trick using MAX() Over() window aggregate function

SELECT DISTINCT
 document,
 article,
 max(CASE WHEN isQR='Y' THEN 'Y' ELSE ' ' END) over() AS CONTAINQR 
from product

'Y' will be sorted before ' ' in Max aggregate