Sticking to your pivot approach:

DECLARE @Table TABLE (Code_NBR VARCHAR(3), List1 VARCHAR(3), List2 VARCHAR(3), List3 VARCHAR(3), List4 VARCHAR(3), List5 VARCHAR(3), List6 VARCHAR(3), List7 VARCHAR(3), List8 VARCHAR(3), List9 VARCHAR(3));
INSERT INTO @Table (Code_NBR, List1, List2, List3, List4, List5, List6, List7, List8, List9) VALUES
('AA1', 'L19', 'L14', 'L15', 'L16', NULL, 'L18', NULL, 'L54',   NULL),
('AB1', 'L60', 'L14', NULL,  'L16', NULL, 'L18', NULL, NULL,    'L56');

SELECT Code_NBR, MAX(List1) AS List1, MAX(List2) AS List2, MAX(List3) AS List3, MAX(List4) AS List4, MAX(List5) AS List5, MAX(List6) AS List6, MAX(List7) AS List7, MAX(List8) AS List8, MAX(List9) AS List9
  FROM (
        SELECT *, 'List' + CAST(ROW_NUMBER() OVER (PARTITION BY Code_NBR ORDER BY List) AS VARCHAR(1)) AS NewList
          FROM @Table
            UNPIVOT (
                     x FOR List IN (List1, List2, List3, List4, List5, List6, List7, List8, List9)
                    ) p 
       ) a
    PIVOT (
           MAX(x) FOR NewList IN (List1, List2, List3, List4, List5, List6, List7, List8, List9)
          ) p2
 GROUP BY Code_nbr;

After an UNPIVOT , Using ROW_NUMBER you can reassign the column names and then PIVOT them back.

Please try the following solution.

It is using XML and XQuery.

CROSS APPLY is tokenizing each row as XML. Amazingly enough that XML is omitting columns with NULL values. By doing that we are shifting real data to the left.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    CODE_NBR VARCHAR(10) PRIMARY KEY, 
    LIST1 CHAR(3),
    LIST2 CHAR(3),
    LIST3 CHAR(3),
    LIST4 CHAR(3),
    LIST5 CHAR(3),
    LIST6 CHAR(3),
    LIST7 CHAR(3),
    LIST8 CHAR(3),
    LIST9 CHAR(3)
);
INSERT @tbl (CODE_NBR, LIST1, LIST2, LIST3, LIST4, LIST5, LIST6, LIST7, LIST8, LIST9) VALUES
('AA1', 'L19', 'L14', 'L15', 'L16', NULL, 'L18', NULL, 'L54', NULL),    
('AB1', 'L60', 'L14', NULL, 'L16', NULL, 'L18', NULL, NULL, 'L56');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

-- after
SELECT CODE_NBR
    , x.value('(/root/*[2]/text())[1]', 'CHAR(3)') AS NewLIST1
    , x.value('(/root/*[3]/text())[1]', 'CHAR(3)') AS NewLIST2
    , x.value('(/root/*[4]/text())[1]', 'CHAR(3)') AS NewLIST3
    , x.value('(/root/*[5]/text())[1]', 'CHAR(3)') AS NewLIST4
    , x.value('(/root/*[6]/text())[1]', 'CHAR(3)') AS NewLIST5
    , x.value('(/root/*[7]/text())[1]', 'CHAR(3)') AS NewLIST6
    , x.value('(/root/*[8]/text())[1]', 'CHAR(3)') AS NewLIST7
    , x.value('(/root/*[9]/text())[1]', 'CHAR(3)') AS NewLIST8
    , x.value('(/root/*[10]/text())[1]', 'CHAR(3)') AS NewLIST9
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);

Output

WITH cte as (
   SELECT CODE_NBR, value, ordinal, row_number() over (partition by CODE_NBR order by ordinal) R
   FROM mytable m
   CROSS APPLY string_split(m.LIST1+','+m.LIST2+','+m.LIST3+','+m.LIST4+','+
        m.LIST5+','+m.LIST6+','+m.LIST7+','+m.LIST8+','+m.LIST9,',',1) x
   WHERE value<>'' 
)
 UPDATE mytable
 SET LIST1 = value1.value,
     LIST2 = value2.value,
     LIST3 = value3.value,
     LIST4 = value4.value,
     LIST5 = value5.value,
     LIST6 = value6.value,
     LIST7 = value7.value,
     LIST8 = value8.value,
     LIST9 = value9.value
FROM mytable
LEFT JOIN cte as value1 ON value1.CODE_NBR=mytable.CODE_NBR and value1.R=1
LEFT JOIN cte as value2 ON value2.CODE_NBR=mytable.CODE_NBR and value2.R=2
LEFT JOIN cte as value3 ON value3.CODE_NBR=mytable.CODE_NBR and value3.R=3
LEFT JOIN cte as value4 ON value4.CODE_NBR=mytable.CODE_NBR and value4.R=4
LEFT JOIN cte as value5 ON value5.CODE_NBR=mytable.CODE_NBR and value5.R=5
LEFT JOIN cte as value6 ON value6.CODE_NBR=mytable.CODE_NBR and value6.R=6
LEFT JOIN cte as value7 ON value7.CODE_NBR=mytable.CODE_NBR and value7.R=7
LEFT JOIN cte as value8 ON value8.CODE_NBR=mytable.CODE_NBR and value8.R=8
LEFT JOIN cte as value9 ON value9.CODE_NBR=mytable.CODE_NBR and value9.R=9
;

SELECT * FROM mytable;

First put the values in a list, and then split them....

see: DBFIDDLE

P.S. The first try (in the dbfiddle) is a naive approach, which should be ignored... 😉

Here is the solution. I have made the solution dynamic. I tried to get the all the columns where you want to do the analysis and used pivot to find out your desired answer.

declare @col1 varchar(MAX);
select @col1 = concat('concat_ws(' , ''',''' , ','
,string_agg(column_name, ','),')') from INFORMATION_SCHEMA.COLUMNS where table_name = 'PLANS' and 
column_name <> 'CODE_NBR';

declare @sql varchar(max)
set @sql = 'select code_nbr, ' +(@col1) +' as [col] into [agg1] from plans';
exec(@sql);

select * from
(
    select 
        a.code_nbr , 
        value as code_value , 
        concat('NewList' ,row_number() over(partition by code_nbr order by code_nbr)) as column_name 
    from agg1 as a cross apply string_split(col, ',') where 
    value <> ''
) as a
Pivot
(
    max(code_value) for column_name
    in (NewList1 , NewList2 , NewList3 , NewList4 , NewList5 , NewList6 ,   NewList7 , NewList8 , NewList9)
) as pivot_tab;

A manual unpivot-repivot without joins, xml/json, dynamic sql or special pivoting operators:

with upvt as (
    select CODE_NBR, idx, val,
         row_number() over (partition by CODE_NBR
                            order by case when val <> '' then 1 else 2 end, idx) as rn
    from PLANS cross apply (values
        (1, LIST1), (2, LIST2), (3, LIST3), (4, LIST4), (5, LIST5),
        (6, LIST6), (7, LIST7), (8, LIST8), (9, LIST9)) as u(idx, val)
)
select CODE_NBR,
    min(case rn when 1 then val end) as LIST1,
    min(case rn when 2 then val end) as LIST2,
    min(case rn when 3 then val end) as LIST3,
    min(case rn when 4 then val end) as LIST4,
    min(case rn when 5 then val end) as LIST5,
    min(case rn when 6 then val end) as LIST6,
    min(case rn when 7 then val end) as LIST7,
    min(case rn when 8 then val end) as LIST8,
    min(case rn when 9 then val end) as LIST9
from upvt
group by CODE_NBR;