CODEWITHSUNDEEP
sqlsql-server-2008-r2
Nest
Nest

Reputation: 341

Move table data from one row to another row if source row is null

I have the table, with some columns contains null values, i tasked to move all the NULL values into left side, so the numeric values are moved from right to left in same order.

(for ex)

Column1   Column2    Column3    Column4
--------------------------------------------
NULL      1          NULL       3
1         NULL       3          2
1         2          3          NULL

Output should be

Column1   Column2    Column3    Column4
--------------------------------------------
1         3          NULL       NULL
1         3          2          NULL
1         2          3          NULL

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

This is a pain, but you can do it using outer apply:

select t.id, v.*
from t outer apply
     (select max(case when i = 1 then colval end) as col1,
             max(case when i = 2 then colval end) as col2,
             max(case when i = 3 then colval end) as col3,
             max(case when i = 4 then colval end) as col4
      from (select row_number() over (order by colnum) as i, v.*
            from (values (1, t.col1), (2, t.col2), (3, t.col3), (4, t.col4)
                 ) v(colnum, colval)
            where colval is not null
           ) v
     ) v;

I should note that the need to do this type of transformation suggests that you have a poor data model. The values in the separate columns should probably be in another table, with one row per id and per column.

Upvotes: 1

Related Questions