Sorry to bother you again. I need to check if the field1= '1' and replace it if it is .

this does but then it puts the 1 in the field 4 ?

thanks so much

;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 or v = '1' 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 or v = '1' 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