DECLARE @a TABLE 
(
	[111] VARCHAR(5),
	[222] VARCHAR(5),
	[333] VARCHAR(5)
)

INSERT INTO @a VALUES
('AAAA',NULL,NULL),
(NULL,NULL,'FFF'),
(NULL,'EEE',NULL),
(NULL,'BBB',NULL),
(NULL,NULL,'CCC'),
(NULL,NULL,'LLL'),
('XXXX',NULL,NULL),
('ZZZZ',NULL,NULL),
(NULL,NULL,'NNN'),
('DDDD',NULL,NULL),
(NULL,NULL,'III');



with 
A as 
(Select *,row_number() over (Order by (select 1)) as rn From @a ),
B as
(Select *,count([111]) over (Order by rn) as ID_111 From A),
C as
(Select *,
        count([222]) over (partition by ID_111 Order by rn) as ID_222,
	max([111]) over (partition by ID_111)  as [_111]
	From B),
D as
(Select *,
        count([333]) over (partition by ID_111,ID_222 Order by rn) as ID_333,
	max([222]) over (partition by ID_111,ID_222)  as [_222]
	From C),
E as
(Select *,
        max([333]) over (partition by ID_111,ID_222,ID_333)  as [_333],
	max(ID_222) over (partition by ID_111) as Count_222,
	max(ID_333) over (partition by ID_111,ID_222) as Count_333
	From D)
Select [_111],[_222],[_333] From E
Where not(Count_222>0 and ID_222 = 0) 
   and not(Count_333>0 and ID_333 = 0) 

SELECT 
	a.a,
	b.a,
	c.a
FROM a
FULL JOIN b
ON
	a.r = b.r
FULL JOIN c
ON
-----Исправление:
	c.r = isnull(a.r,b.r)

From E
Where not(Count_222>0 and ID_222 = 0 and Count_333=0) 
   and not(Count_333>0 and ID_333 = 0)

INSERT INTO @a VALUES
(NULL,NULL,'LLL'),
('AAAA',NULL,NULL),
(NULL,'BBB','CCC'),
('XXXX',NULL,'NNN'),
('DDDD',NULL,NULL),
(NULL,NULL,'III'),
(NULL,'QQQ',NULL),
(NULL,'WWW',NULL),
(NULL,'EEE',NULL),
(NULL,'RRR',NULL)