frecal (3/21/2013)

---

Thanks Very much for this i was trying to also get it to work using this method (it is easier to follow). but get an error like

Msg 1033, Level 15, State 1, Line 34

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

should i have just replaced the Select statement?

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,

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 --<<-- DELETE THIS

)

UPDATE t

SET Field1 = sf.field1_new

,Field2 = sf.Field2_new

,Field3 = sf.Field3_new

,Field4 = sf.Field4_new

FROM @thedatalookslikethis t

JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis