Change all values in a particular column based on one column value or condition in SQL using CTE-CodePudding

I have a table as below:

p_id	m_id	1_m_ret	2_m_ret	3_m_ret	4_m_ret	5_m_ret
p_1	m_1	NULL	1.1	3.1	NULL	5.5
p_1	m_2	1.23	2.5	3.6	3.3	1.5
p_1	m_3	1.10	1.6	3.5	1.8	3.2

I want to change the values of columns based on p_id to NULL If there's any NULL in the column for the p_id and the result to be like below table:

p_id	m_id	1_m_ret	2_m_ret	3_m_ret	4_m_ret	5_m_ret
p_1	m_1	NULL	1.1	3.1	NULL	5.5
p_1	m_2	NULL	2.5	3.6	NULL	1.5
p_1	m_3	NULL	1.6	3.5	NULL	3.2

I am able to achieve this using custom functions and stored procedure on local. However i need to achieve it using CTE(without function or stored procedure), Any suggestions?

CodePudding user response：

Your question could use a little clarifying but I think I understand it. If there's any NULL in the column for the p_id, you want the column to be NULL everywhere for the p_id. Otherwise, just leave it as is.

So if that's accurate, try this:

CASE WHEN FIRST_VALUE(1_m_ret) OVER (PARTITION BY p_id ORDER BY 1_m_ret ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) IS NULL THEN NULL ELSE 1_m_ret END

CodePudding user response：

The most accurate way to do this is a conditional windowed count

CASE WHEN
    COUNT(CASE WHEN 1_m_ret IS NULL THEN 1 END) OVER (PARTITION BY p_id) > 0
  THEN NULL ELSE 1_m_ret END