Put Multiple Columns data into Consecutive Columns

I’m not sure how to even word/explain what I’m looking for. I have data like this:

I want them to be put into consecutive columns like this, basically removing gaps between:

I’ve tried unpivoting the columns into one column them pivoting them back and they just go back where they were. I also tried dynamic SQL and it still put them in the same place they were originally in, so I’m not sure how I can accomplish what I’m trying to do. This is sample data

--DROP TABLE PLANS;
create table PLANS
(CODE_NBR nvarchar(3),
ListTable1 nvarchar(10), 
ListTable2 nvarchar(10), 
ListTable3 nvarchar(10), 
ListTable4 nvarchar(10), 
ListTable5 nvarchar(10), 
ListTable6 nvarchar(10), 
ListTable7 nvarchar(10), 
ListTable8 nvarchar(10), 
ListTable9 nvarchar(10)
);
insert into PLANS values('AA1','L19','L14','L15','L16','','L18','','L54',''),('AB1','L60','L14','','L16','','L18','','','L56'),('AC1','','','','','','','','','L56');

WITH 
UNPVT AS
(SELECT *
FROM (SELECT A.CODE_NBR
,ListTable1 L1
,ListTable2 L2
,ListTable3 L3
,ListTable4 L4
,ListTable5 L5
,ListTable6 L6
,ListTable7 L7
,ListTable8 L8
,ListTable9 L9
FROM PLANS A) TMP
UNPIVOT
(LISTS FOR LISTALL IN (L1,L2,L3,L4,L5,L6,L7,L8,L9)) UNPIV),

LISTCOUNT AS 
(SELECT CODE_NBR, COUNT(LISTS) LCOUNT, MIN(lists) firstlist, max(lists) lastlist
FROM UNPVT GROUP BY CODE_NBR),

PVT AS
(SELECT *
FROM
(SELECT * FROM UNPVT) T1
PIVOT ( MAX(LISTS)
    FOR LISTALL IN (L1,L2,L3,L4,L5,L6,L7,L8,L9)) T1
)
SELECT * FROM PVT

>Solution :

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

Output