declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))
insert @thedatalookslikethis  
      select 1, 'a', null, 'b', null
union select 2, null, '9', null, '6'
union select 3, '2', null, null, '8'
union select 4, 'a', null, null, null
union select 5, null, 'a', null, null
union select 6, null, null, 'a', null
union select 7, null, null, null, 'a'
union select 8, null, null, 'a', 'b'
union select 9, 'a', null, 'b', 'c'
union select 10, 'a', 'b', null, 'c'
union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields
AS
(
SELECT   t.id
        ,MAX(CASE WHEN f=1 THEN v ELSE NULL END) Field1
        ,MAX(CASE WHEN f=2 THEN v ELSE NULL END) Field2
        ,MAX(CASE WHEN f=3 THEN v ELSE NULL END) Field3
        ,MAX(CASE WHEN f=4 THEN v ELSE NULL END) Field4
FROM @thedatalookslikethis t
CROSS APPLY (SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY (CASE WHEN v IS NULL THEN 5 ELSE f END)) f, v 
             FROM (VALUES (1,field1),(2,field2),(3,field3),(4,field4)) nn(f,v) 
             ORDER BY (CASE WHEN v IS NULL THEN 5 ELSE f END)) c
GROUP BY t.id
)

UPDATE  t
SET     Field1 = sf.Field1 
       ,Field2 = sf.Field2 
       ,Field3 = sf.Field3
       ,Field4 = sf.Field4
FROM @thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id
             
SELECT * FROM @thedatalookslikethis              

SELECT
    t.id,
    COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,
    CASE WHEN t.field1 IS NOT NULL THEN COALESCE(t.field2, t.field3, t.field4)
         WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
         WHEN t.field3 IS NOT NULL THEN t.field4
         ELSE NULL END AS field2_new,
    CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
         WHEN (t.field1 IS NOT NULL AND t.field3 IS NOT NULL) OR
              (t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4
         ELSE NULL END AS field3_new,
    CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL AND t.field3 IS NOT NULL THEN t.field4
         ELSE NULL END AS field4_new
FROM @thedatalookslikethis t
ORDER BY t.id