sql无级分类语句

Question

现有如下数据结构
  corpname                   level
  A001                       0001
  A002                       00010001
  A003                       00010002
  A004                       000100010001
现求一条sql，实现如下效果(或类似)
 A001    A002    A004
 A001    A003    null不要存储过程和函数，而且要能动态扩展，比如再加一条 A005    0001000100010001，结果为
A001    A002    A004    A005
A001    A003    NULL    NULL

Answer 1

 create table corp( corpname nvarchar(100), level nvarchar(200))
  go
  insert corp(corpname, level)
   select 'A001', '0001' union all
   select 'A002', '00010001' union all
   select 'A003', '00010002' union all
   select 'A004', '000100010001'
  go
  
--现求一条sql，实现如下效果(或类似)
-- A001 A002 A004
-- A001 A003 null;with cte as(
select rno = ROW_NUMBER() over(order by len(level) desc),* 
from corp cp
where not exists(select 1 
from corp c 
where CHARINDEX(cp.level,c.level)>0
and LEN(cp.level)<LEN(c.level)
)
),
cte2 as(
select * 
from cte c
union all
select c.rno,cp.*
from corp cp
inner join cte c
on CHARINDEX(cp.level,c.level)>0
and LEN(cp.level)<>LEN(c.level)
)
select rno,
corpname 
from cte2
order by rno,corpname
for xml path('')
--------------------------------------------------
--这方法一步一步来，只能最后得到个xml，但xml不会操作，自己学吧
<rno>1</rno>
<corpname>A001</corpname>
<rno>1</rno>
<corpname>A002</corpname>
<rno>1</rno>
<corpname>A004</corpname>
<rno>2</rno>
<corpname>A001</corpname>
<rno>2</rno>
<corpname>A003</corpname>

Answer 2

--或者行转列，但要指定字段名，如果要动态，只能用动态语句完成
select rno,[A001],[A002],[A003],[A004]
from (select rno, corpname from cte2) spvt
pivot (MAX(corpname) for corpname in([A001],[A002],[A003],[A004])) pvt
----------------------------------
rno A001 A002 A003 A004
1 A001 A002 NULL A004
2 A001 NULL A003 NULL

Answer 3

--帮人帮到底
create table corp( corpname nvarchar(100), level nvarchar(200))
  go
  insert corp(corpname, level)
   select 'A001', '0001' union all
   select 'A002', '00010001' union all
   select 'A003', '00010002' union all
   select 'A004', '000100010001'
  go
  
--现求一条sql，实现如下效果(或类似)
-- A001 A002 A004
-- A001 A003 null;with cte as(
select rno = ROW_NUMBER() over(order by len(level) desc),* 
from corp cp
where not exists(select 1 
from corp c 
where CHARINDEX(cp.level,c.level)>0
and LEN(cp.level)<LEN(c.level)
)
)
select *
,XM=REPLACE(REPLACE(REPLACE((select corpname 
from corp cp 
where CHARINDEX(cp.level,c.level)>0
for xml path('') )
,'</corpname><corpname>',' '),'<corpname>',''),'</corpname>','')
from cte c 
-----------------------------------
rno corpname level XM
1 A004 000100010001 A001 A002 A004
2 A003 00010002 A001 A003