sql server 2008 - SQL Merge matching rows in SQL table -

here table schema

table1 { column1 nvarchar(max), column2 nvarchar(max) } 

here sample data

column1            column2  tom                blue tom                 gary               green gary               yellow sam                  sam                red   

i update column2 if column1 value duplcated , either 1 of rows in column2 empty , empty cell replaced non empty cell.

for example desired output above sample data

 column1            column2      tom                blue     tom                blue     gary               green     gary               yellow     sam                red      sam                red   

the below update maximum value, ignoring nulls or empty strings.

if there 1 distinct possibility of course 1 chosen.

;with t  ( select *,         max(nullif(column2,'')) on (partition column1) c2 yourtable ) update t set column2 = c2 column2 null or column2 = '';