Below is an example table, I am trying to figure out a way to write a query to put a Y in every cell under READY if all of the STOCK cells are empty for each ID row. So a Y would only show up in the READY columns for ID 1 since OS is in one of the columns for each other ID.
| ID | STOCK | READY |
|---|---|---|
| 1 | ||
| 1 | ||
| 1 | ||
| 2 | OS | |
| 2 | ||
| 2 | ||
| 3 | OS | |
| 3 |
My first thought is to use a case statement but that puts a Y in every column that has blank STOCK instead of checking if all of the STOCK for each ID is empty. Any ideas how I could accomplish this?
SELECT ID,
STOCK,
CASE WHEN STOCK = '' THEN 'Y'
ELSE '' END AS [READY]
FROM TABLE
>Solution :
You can use window functions:
select id, stock,
case when count(stock) over(partition by id) = 0 then 'Y' end as ready
from mytable
The window count takes in account non-null values of stock in rows sharing the same id.