sql server - Row-wise shifting pivoted data -

i need following data

id  1            2            3            4            5 --- ------------ ------------ ------------ ------------ ----------- 1   null         null         null         null         level 1 2   null         null         null         level 1      level 2 3   null         null         level 1      level 2      level 3 4   null         level 1      level 2      level 3      level 4 5   level 1      level 2      level 3      level 4      level 5 

to transformed as:

id  level1       level2       level3       level4       level5 --- ------------ ------------ ------------ ------------ ------------ 1   level 1      null         null         null         null 2   level 1      level 2      null         null         null 3   level 1      level 2      level 3      null         null 4   level 1      level 2      level 3      level 4      null 5   level 1      level 2      level 3      level 4      level 5 

i.e. in every row data of columns 1,2,3,4,5 shifted left number of positions necessary place first non-null value among them first position.

data originated self-referencing table:

create table data (id int not null, parentid int, name varchar(50)) 

by applying following statement (which wrapped table-valued function) subset of records table:

with path(id, parentid, name, level) (     select id, parentid, name, 0 data id = @id     union     select d.id, d.parentid, d.name, p.level-1     data d         join path p on p.parentid = d.id ) select     [1], [2], [3], [4], [5]     (select 5 + level level, name path) s     pivot (max(name) level in ([1], [2], [3], [4], [5])) p 

currently transform realized bunch of case operators after data pivoted. feel perhaps there should can done before pivoting (to make more elegant and/or effective).

ideally see here various approaches solving task possible (any changes before or after pivoting, not matter).

number of levels known , constant (and equals 5 in case).

sql fiddle sample

the sql fiddle useful. got wanted changing function. here revised function:

create function ftinfo_new(@id int) returns table return     path(id, parentid, name, level)           (select id, parentid, name, 0            data            id = @id            union            select d.id, d.parentid, d.name, p.level-1            data d join                 path p                 on p.parentid = d.id          )     select [1], [2], [3], [4], [5]     (select 1 - level level, name           path          ) s         pivot (max(name) level in ([1], [2], [3], [4], [5])); 

you can increasing levels instead of decreasing them:

create function ftinfo_new(@id int) returns table return     path(id, parentid, name, level)         (         select id, parentid, name, 1         data         id = @id         union         select d.id, d.parentid, d.name, p.level+1         data d join              path p              on p.parentid = d.id     )     select [1], [2], [3], [4], [5]     (select level level, name           path          ) s         pivot (max(name) level in ([1], [2], [3], [4], [5])) p