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

Question

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

Gordon Linoff · Accepted Answer · 2017-03-07 12:47:00Z

1

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.

answered Mar 7, 2017 at 12:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related