SSIS Derived Column tool - moving Null values

[maccaman]

Hi everyone,
I was hoping someone could help me create an expression in the derived column tool where for every row that has Null values, the null values are moved to the right and are replaced by data that may exist in columns to the left of the Null Values. Confused? Here is what I am trying to achieve in table form (note different columns are indicated by commas, and the Row column is there for easy reference (not actually in my data)).

ROW, Level 1_id, Level 2_id, Level 3_id, Level 4_id, Level 5_id, Level 6_id, Level 7_id, Level 8_id,
1, AX, T1, NULL, NULL, NULL, NULL, J1, K1,
2, AX, T1, U1, V1, NULL, NULL, J1, K1,
3, AX, T2, U1, V1, W2, NULL, J2, K2,
4, AX, T2, U2, V2, W4, X1, J3, K3,
5, AX, T3, U3, V3, W8, X5, J3, K4,
6, AX, T3, U3, V4, W4, X4, J6, K5,
7, AX, T4, NULL, NULL, NULL, NULL, NULL, K5,
8, AX, T4, NULL, NULL, W3, X4, J9, K8,
9, AX, T4, U4, NULL, NULL, X4, J10, K12,
10, AX, T4, U5, V8, W1, X7, J31, K12,
11, AX, T5, U7, V8, NULL, NULL, NULL, K17,


Obviously this table has 8 columns called level 1 through to Level 8, and some of these contain number Null Values between columns 1 and 8.
What I meant when I said that I would like to have these null values moved to the left is that I would like to create an expression that results in the following table (again row column in there for easy reference):

ROW, Level 1_id, Level 2_id, Level 3_id, Level 4_id, Level 5_id, Level 6_id, Level 7_id, Level 8_id,
1, AX, T1, J1, K1, NULL, NULL, NULL, NULL,
2, AX, T1, U1, V1, J1, K1, NULL, NULL,
3, AX, T2, U1, V1, W2, J2, K2, NULL,
4, AX, T2, U2, V2, W4, X1, J3, K3,
5, AX, T3, U3, V3, W8, X5, J3, K4,
6, AX, T3, U3, V4, W4, X4, J6, K5,
7, AX, T4, K5, NULL, NULL, NULL, NULL, NULL,
8, AX, T4, W3, X4, J9, K8, NULL, NULL,
9, AX, T4, U4, X4, J10, K12, NULL, NULL,
10, AX, T4, U5, V8, W1, X7, J31, K12,
11, AX, T5, U7, V8, K17, NULL, NULL, NULL,


If you compare Row 1 in the first table with row 1 in the second table you can see that the Null Values have been moved across to the right, and the values that were in the columns titled "Level 7" and "Level 8" have been moved to the columns where the first two Null values appeared for that row. Hopefully this makes sense.

The expression I have come up with isn't correct because it does move the values across, but I need to replace the celss that had values with nulls.

So for each level I have the following expressions:
(ISNULL(Level_2_ID) == TRUE ? (ISNULL(Level_3_ID) == TRUE ? (ISNULL(Level_4_ID) == TRUE ? (ISNULL(Level_5_ID) == TRUE ? (ISNULL(Level_6_ID) == TRUE ? (ISNULL(Level_7_ID) == TRUE ? Level_8_ID : Level_7_ID) : Level_6_ID) : Level_5_ID) : Level_4_ID) : Level_3_ID) : Level_2_ID)

(ISNULL(Level_3_ID) == TRUE ? (ISNULL(Level_4_ID) == TRUE ? (ISNULL(Level_5_ID) == TRUE ? (ISNULL(Level_6_ID) == TRUE ? (ISNULL(Level_7_ID) == TRUE ? Level_8_ID : Level_7_ID) : Level_6_ID) : Level_5_ID) : Level_4_ID) : Level_3_ID)

(ISNULL(Level_4_ID) == TRUE ? (ISNULL(Level_5_ID) == TRUE ? (ISNULL(Level_6_ID) == TRUE ? (ISNULL(Level_7_ID) == TRUE ? Level_8_ID : Level_7_ID) : Level_6_ID) : Level_5_ID) : Level_4_ID)

ect down to

(ISNULL(Level_7_ID) == TRUE ? Level_8_ID : Level_7_ID)

But again, these are wrong and I am really stuck.

I also have a column called 'Move Number' that lists the number of nulls in a particular row. For example, in row one, the move number is 4, in row 2 the "Move Number" is 2.
Can anyone please help me?

Thanks,
Maccaman