有一树形结构的表：
create table #aa(xx varchar(20),xx_上级 varchar(20),级别 varchar(20))
insert #aa
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
select * from #aa
希望通过SQL得到以下结果：
xx1 xx2 xx3 xx4 xx5
-------------------------------------------
A A1 A1a A1a01 A1a01A
A null A2a null A2a01A
B null null null B1a01A
------解决方案--------------------
- SQL code
IF OBJECT_ID('[TB]') IS NOT NULL DROP TABLE [TB]GOCREATE TABLE TB(XX VARCHAR(20),XX_上级 VARCHAR(20),级别 VARCHAR(20))INSERT TBSELECT 'A',NULL,1 UNION ALLSELECT 'A1','A',2 UNION ALLSELECT 'A1A','A1',3 UNION ALLSELECT 'A1A01','A1A',4 UNION ALLSELECT 'A1A01A','A1A01',5 UNION ALLSELECT 'A2A','A',3 UNION ALLSELECT 'A2A01A','A2A',5 UNION ALLSELECT 'B',NULL,1 UNION ALLSELECT '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],[email protected]+' FROM ( SELECT XX,级别=''XX''+LTRIM(级别),X1,X2 FROM T ) A PIVOT(MAX(XX) FOR 级别 IN([email protected]+'))B') /*xx1 xx2 xx3 xx4 xx5-------------------- -------------------- -------------------- -------------------- --------------------A A1 A1a A1a01 A1a01AA NULL A2a NULL A2a01AB NULL NULL NULL B1a01A(3 行受影响)*/