Reversing hierarchy columns

Question

I have several hierarchy columns that I've populated in a table. I would like to populate the reverse of those columns, but don't know how to handle an unknown number of null values.

I could populate these columns with quite a few update statements, but I think there's got to be an easier/cleaner way.

Answer 1

If the source columns are ordered such that they always have 0 or more NOT NULL columns followed by the remainder of the columns being NULL then you can use (Fiddle)

UPDATE mt
        SET RevL1 = CHOOSE(idx-0, L1, L2, L3, L4, L5), 
            RevL2 = CHOOSE(idx-1, L1, L2, L3, L4, L5), 
            RevL3 = CHOOSE(idx-2, L1, L2, L3, L4, L5), 
            RevL4 = CHOOSE(idx-3, L1, L2, L3, L4, L5), 
            RevL5 = CHOOSE(idx-4, L1, L2, L3, L4, L5)
FROM @MyTable mt
CROSS APPLY (VALUES(
        CASE 
            WHEN L5 = L5 THEN 5
            WHEN L4 = L4 THEN 4
            WHEN L3 = L3 THEN 3
            WHEN L2 = L2 THEN 2
            WHEN L1 = L1 THEN 1
            ELSE 0
        END 
))V(idx)

idx is the index of the last NOT NULL column