求SQL：字符类型的行列转换
有一树形结构的表：
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得到以下结果：
xx1xx2xx3xx4xx5
-------------------------------------------
AA1A1aA1a01A1a01A
AnullA2anullA2a01A
BnullnullnullB1a01A
[解决办法]
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],'+@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 A1a01AA NULL A2a NULL A2a01AB NULL NULL NULL B1a01A(3 行受影响)*/