Query for checking if every cell is null in a column by id-CodePudding

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

CodePudding user response：

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.