IF OBJECT_ID('[TB]') IS NOT NULL 
    DROP TABLE [TB]
GO
CREATE TABLE TB(XX VARCHAR(20),XX_上级 VARCHAR(20),级别 VARCHAR(20))
INSERT TB
SELECT 'A',NULL,1 UNION ALL
SELECT 'A1','A',2 UNION ALL
SELECT 'A1A','A1',3 UNION ALL
SELECT 'A1A01','A1A',4 UNION ALL
SELECT 'A1A01A','A1A01',5 UNION ALL
SELECT 'A2A','A',3 UNION ALL
SELECT 'A2A01A','A2A',5 UNION ALL
SELECT 'B',NULL,1 UNION ALL
SELECT 'B1A01A','B',5

-->SQL查询如下：
DECLARE @S VARCHAR(MAX),@A VARCHAR(MAX)
SELECT @S=ISNULL(@S+',','')+'XX'+LTRIM(级别) FROM TB GROUP BY 级别
SELECT @A=STUFF(@S,1,CHARINDEX(',',@S),'')
EXEC('
    WITH T AS
    (
        SELECT *,X1=XX_上级,X2=XX_上级+LTRIM(ROW_NUMBER()OVER(ORDER BY XX))  FROM TB A
        WHERE EXISTS(SELECT 1 FROM TB WHERE A.XX_上级=XX AND XX_上级 IS NULL)
        UNION ALL
        SELECT A.*,B.X1,B.X2 FROM TB A JOIN T B ON A.XX_上级=B.XX
    )
    SELECT XX1=[X1],'+@A+'
    FROM (
        SELECT XX,级别=''XX''+LTRIM(级别),X1,X2 FROM T
        ) A
        PIVOT(MAX(XX) FOR 级别 IN('+@S+'))B'
)
    
/*
xx1                  xx2                  xx3                  xx4                  xx5
-------------------- -------------------- -------------------- -------------------- --------------------
A                    A1                   A1a                  A1a01                A1a01A
A                    NULL                 A2a                  NULL                 A2a01A
B                    NULL                 NULL                 NULL                 B1a01A

(3 行受影响)
*/