求sql：字符类型的行列转换（2）

Question

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 'A01','A',2 UNION ALL
SELECT 'A02','A',2 UNION ALL
SELECT 'A02B','A02',3 UNION ALL
SELECT 'A02A01','A02',4 UNION ALL
SELECT 'A02B01','A02B',4 UNION ALL
SELECT 'A01A01A','A01',5 UNION ALL
SELECT 'A02A01A','A02A01',5 UNION ALL
SELECT 'A02B01A','A02B01',5 UNION ALL
SELECT 'B',NULL,1 UNION ALL
SELECT 'B01A01A','B',5 UNION ALL
SELECT 'C',NULL,1 UNION ALL
SELECT 'C01A','C',3 UNION ALL
SELECT 'C01A01','C01A',4 UNION ALL
SELECT 'C01A02','C01A',4 UNION ALL
SELECT 'C01A01A','C01A01',5 UNION ALL
SELECT 'C01A01B','C01A01',5 UNION ALL
SELECT 'C01A02A','C01A02',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                    A01                  NULL                 NULL                 A01A01A
A                    A02                  A02B                 A02B01               A02B01A
B                    NULL                 NULL                 NULL                 B01A01A
C                    NULL                 C01A                 C01A02               C01A02A(4 行受影响)
*/
--而我想要的结果是:
/*
XX1                  XX2                  XX3                  XX4                  XX5
-------------------- -------------------- -------------------- -------------------- --------------------
A                    A01                  NULL                 NULL                 A01A01A
A                    A02                  NULL                 A02A01               A02A01A
A                    A02                  A02B                 A02B01               A02B01A
B                    NULL                 NULL                 NULL                 B01A01A
C                    NULL                 C01A                 C01A01               C01A01A
C                    NULL                 C01A                 C01A01               C01A01B
C                    NULL                 C01A                 C01A02               C01A02A(4 行受影响)
*/请大虾帮帮忙了，感激涕零

Answer 1

想改，上面的sql是第一个帖子得到的回复，可是结果与我想要的结果有点差别，希望哪位大侠再帮我改一下，得到下面那个结果集

Answer 2

CTE，今天刚学，今晚回去研究一下

Answer 3

IF OBJECT_ID('[TB]') IS NOT NULL 
    DROP TABLE [TB]
GO
CREATE TABLE TB(XX VARCHAR(20),XX_up VARCHAR(20),deep VARCHAR(20))
INSERT TB
SELECT 'A',NULL,1 UNION ALL
SELECT 'A01','A',2 UNION ALL
SELECT 'A02','A',2 UNION ALL
SELECT 'A02B','A02',3 UNION ALL
SELECT 'A02A01','A02',4 UNION ALL
SELECT 'A02B01','A02B',4 UNION ALL
SELECT 'A01A01A','A01',5 UNION ALL
SELECT 'A02A01A','A02A01',5 UNION ALL
SELECT 'A02B01A','A02B01',5 UNION ALL
SELECT 'B',NULL,1 UNION ALL
SELECT 'B01A01A','B',5 UNION ALL
SELECT 'C',NULL,1 UNION ALL
SELECT 'C01A','C',3 UNION ALL
SELECT 'C01A01','C01A',4 UNION ALL
SELECT 'C01A02','C01A',4 UNION ALL
SELECT 'C01A01A','C01A01',5 UNION ALL
SELECT 'C01A01B','C01A01',5 UNION ALL
SELECT 'C01A02A','C01A02',5  
 
;WITH CTE1 AS(
 SELECT path_str=CONVERT(VARCHAR(2000),'<row column=''xx1'' value='''+XX+''' />'),xx,deep=1 FROM TB WHERE deep=1
 UNION ALL
 SELECT path_str=CONVERT(VARCHAR(2000),a.path_str+'<row column=''xx'+CONVERT(VARCHAR(5),b.deep)+''' value='''+b.XX+''' />')
,b.xx,deep=CONVERT(INT,a.deep+1)
 FROM CTE1 AS a INNER JOIN TB AS b ON b.XX_up=a.XX
)
SELECT 
xx1=CONVERT(XML,path_str).value('(/row[@column=''xx1'']/@value)[1]','varchar(20)')
,xx2=CONVERT(XML,path_str).value('(/row[@column=''xx2'']/@value)[1]','varchar(20)')
,xx3=CONVERT(XML,path_str).value('(/row[@column=''xx3'']/@value)[1]','varchar(20)')
,xx4=CONVERT(XML,path_str).value('(/row[@column=''xx4'']/@value)[1]','varchar(20)')
,xx5=CONVERT(XML,path_str).value('(/row[@column=''xx5'']/@value)[1]','varchar(20)')
FROM CTE1 AS a   
WHERE NOT EXISTS(SELECT 1 FROM CTE1 AS x WHERE x.deep>a.deep AND CHARINDEX(a.path_str, x.path_str)>0)
ORDER BY xx1,xx2,xx3,xx4,xx5
 
 

Answer 4

CTE逆归对数据大的处理是比较无奈的.

Answer 5

提供另外一种非递归方案,效率应该要高:
IF OBJECT_ID('[TB]') IS NOT NULL 
DROP TABLE [TB]
GO
CREATE TABLE TB(XX VARCHAR(20),XX_up VARCHAR(20),deep VARCHAR(20))
INSERT TB
SELECT 'A',NULL,1 UNION ALL
SELECT 'A01','A',2 UNION ALL
SELECT 'A02','A',2 UNION ALL
SELECT 'A02B','A02',3 UNION ALL
SELECT 'A02A01','A02',4 UNION ALL
SELECT 'A02B01','A02B',4 UNION ALL
SELECT 'A01A01A','A01',5 UNION ALL
SELECT 'A02A01A','A02A01',5 UNION ALL
SELECT 'A02B01A','A02B01',5 UNION ALL
SELECT 'B',NULL,1 UNION ALL
SELECT 'B01A01A','B',5 UNION ALL
SELECT 'C',NULL,1 UNION ALL
SELECT 'C01A','C',3 UNION ALL
SELECT 'C01A01','C01A',4 UNION ALL
SELECT 'C01A02','C01A',4 UNION ALL
SELECT 'C01A01A','C01A01',5 UNION ALL
SELECT 'C01A01B','C01A01',5 UNION ALL
SELECT 'C01A02A','C01A02',5 DECLARE @Sql VARCHAR(MAX)
;WITH deep1 AS (SELECT * FROM TB WHERE deep=1)
,deep2 AS (SELECT * FROM TB WHERE deep=2)
,deep3 AS (SELECT * FROM TB WHERE deep=3)
,deep4 AS (SELECT * FROM TB WHERE deep=4)
,deep5 AS (SELECT * FROM TB WHERE deep=5)
,deep1_2 AS(SELECT path_str='xx1='''+a.xx+''''+',xx2='+ISNULL(''''+b.xx+'''','NULL'),b.xx FROM deep1 AS a LEFT JOIN deep2 as B ON b.xx_up=a.xx)
,deep2_3 AS(SELECT path_str=a.path_str+',xx3='+ISNULL(''''+b.xx+'''','NULL'),b.xx FROM deep1_2 AS a LEFT JOIN deep3 as B ON (CHARINDEX(''''+b.xx_up+'''',a.path_str)>0))
,deep3_4 AS(SELECT path_str=a.path_str+',xx4='+ISNULL(''''+b.xx+'''','NULL'),b.xx FROM deep2_3 AS a LEFT JOIN deep4 as B ON (CHARINDEX(''''+b.xx_up+'''',a.path_str)>0))
,deep4_5 AS(SELECT path_str=a.path_str+',xx5='+ISNULL(''''+b.xx+'''','NULL'),b.xx FROM deep3_4 AS a LEFT JOIN deep5 as B ON (CHARINDEX(''''+b.xx_up+'''',a.path_str)>0))
SELECT @Sql=STUFF((SELECT ' UNION ALL SELECT '+path_str  FROM deep4_5 FOR XML PATH('')),1,10,'')+ ' ORDER BY xx1,xx2,xx3,xx4,xx5'EXEC( @Sql)