Fill null columns between not null columns with default value

Question

I am trying to fill all NULL columns between specified columns.

My Table:

I want to fill every column which is NULL if a higher level has a value in it.

For example:

• Row with ID 1 Level3 should have a default value ('def')
• Row with ID 2 is ok. Last filled Column is level4 and no column after that has a value in it.
• Row with ID 3 Level5, Level6, Level7, Level8, Level9 should have a default value ('def')
• Row with ID 4 Level4, Level5, Level6, Level8, Level9 should have a default value ('def')

SQL Fiddle

Answer 1

This is the best I could think of:

update #Levels
set 
level1 = CASE WHEN COALESCE(level2, level3, level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level1 ELSE ISNULL(level1, 'def') END,
level2 = CASE WHEN COALESCE(level3, level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level2 ELSE ISNULL(level2, 'def') END,
level3 = CASE WHEN COALESCE(level4, level5, level6, level7, level8, level9, level10) IS NULL THEN level3 ELSE ISNULL(level3, 'def') END,
level4 = CASE WHEN COALESCE(level5, level6, level7, level8, level9, level10) IS NULL THEN level4 ELSE ISNULL(level4, 'def') END,
level5 = CASE WHEN COALESCE(level6, level7, level8, level9, level10) IS NULL THEN level5 ELSE ISNULL(level5, 'def') END,
level6 = CASE WHEN COALESCE(level7, level8, level9, level10) IS NULL THEN level6 ELSE ISNULL(level6, 'def') END,
level7 = CASE WHEN COALESCE(level8, level9, level10) IS NULL THEN level7 ELSE ISNULL(level7, 'def') END,
level8 = CASE WHEN COALESCE(level9, level10) IS NULL THEN level8 ELSE ISNULL(level8, 'def') END,
level9 = CASE WHEN COALESCE(level10, null) IS NULL THEN level9 ELSE ISNULL(level9, 'def') END

Looks messy, but does the job

SQL Fiddle