sqlsql-serversql-server-2019

Reversing hierarchy columns


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.

Sample Existing Table

What I'd like the table to look like

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


Solution

  • 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