declare @i int,@sql varchar(8000)
set @sql=''
set @i=1
while @i<=N ---N为你的catn中的N的值
begin
if @i<>1
set @sql=@sql+' union all '
set @sql=@sql+' select id,cat'+cast(@i as varchar)+'as Cat from tableA where id='+cast(@i as varchar)+' '
set @i=@i+1
end
exec(@sql)
set @sql=''
set @i=1
while @i<=N ---N为你的catn中的N的值
begin
if @i<>1
set @sql=@sql+' union all '
set @sql=@sql+' select id,cat'+cast(@i as varchar)+'as Cat from tableA where id='+cast(@i as varchar)+' '
set @i=@i+1
end
exec(@sql)
select id, cat1 where depth = 1
union
select id, cat2 where depth = 2
......
union
select id, catn where depth = n
select id, cat1 where depth = 1
union
select id, cat2 where depth = 2
......
union
select id, catn where depth = n
select id, cat1 cat where depth = 1
union
select id, cat2 where depth = 2
......
union
select id, catn where depth = n
select id,
case
when catn is not null then catn
when catn-1 is not null then catn-1
…………
when catn-n+1 is not null then catn-n+1
end AS cat
from A
select @sql='select id,case depth',@i=max(depth) from tba
while @i>0
select @sql=@sql+char(13)+' when '+cast(@i as varchar)
+' then cat'+cast(@i as varchar)
,@i=@i-1
set @sql=@sql+' end as cat from tba'exec(@sql)