1

I want to create a new column called NewHeight that takes the single value found in the Height column and replicates it for all the same Name. This is the query I am using.

Select 
Name,
Height,
case when Height is not null then max(Height) over(partition by Name) end as NewHeight
from MyTable

This is my output:

Name    Height  NewHeight
Johny       
Johny   5.6      5.6
Johny       
Mike        
Mike        
Mike    6.1      6.1

This is desired output :

Name    Height  NewHeight
Johny            5.6
Johny   5.6      5.6
Johny            5.6
Mike             6.1
Mike             6.1
Mike    6.1      6.1
luanstat
  • 65
  • 1
  • 9

2 Answers2

4

You are using is not null when you should be using is null. But, coalesce() is more concise:

Select Name, Height,
       coalesce(Height, max(Height) over (partition by Name) end as NewHeight
from MyTable;

Or, if you want the same value on each row, you don't even need conditional logic:

Select Name, Height,
       max(Height) over (partition by Name) as NewHeight
from MyTable;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Use coalesce() :

Select  Name, Height,
        coalesce(Height, max(Height) over(partition by Name)) as NewHeight
from MyTable
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52