父子结构数据的一种转转置

with business as 

(

select 1 id ,'中国' as name,null pid union all

select 2 id ,'江苏' as name,1 pid union all

select 3 id ,'山东' as name,1 pid union all

select 4 id ,'广东' as name,1 pid union all

select 5 id ,'南京' as name,2 pid union all

select 6 id ,'苏州' as name,2 pid union all

select 7 id ,'常州' as name,2 pid union all

select 8 id ,'济南' as name,3 pid union all

select 9 id ,'青岛' as name,3 pid union all

select 10 id ,'广州' as name,4 pid union all

select 11 id ,'深圳' as name,4 pid union all

select 12 id ,'六合' as name,5 pid union all

select 13 id ,'龙池' as name,12 pid 





)



select * from business

现在只想起来使用union all一步步的联接起来,太麻烦了,层数不一样写的语句也不一样,不方便.
为了好调用,最好是表函数,或者视图来实现.

想要的结果如下:
就是把各记录的所有父节点放在前面,节点的深度未知,这里为了实例写的是5层.有时是4层,有时是6层,但一般不会超过10层.

id lev0 lev1 lev2 lev3 lev4
1 中国
2 中国 江苏
3 中国 山东
4 中国 广东
5 中国 江苏 南京
6 中国 江苏 苏州
7 中国 江苏 常州
8 中国 山东 济南
9 中国 山东 青岛
10 中国 广东 广州
11 中国 广东 深圳
12 中国 江苏 南京 六合
13 中国 江苏 南京 六合 龙池
------解决方案--------------------

引用:

Quote: 引用:

存储过程也可以作为一个表来使用，你返回表变量就OK

你可能没理解我全部的意思,存储过程返回查询结果.但是这个查询结果没法像使用
select * from (pro(@var))这样来使用.
存储过程的结果只能insert 到其它表中去,然后才能使用.
因为我这里,表的列数是不固定的,所以没有固定的表可以被insert,只能使用动态SQL写,但是动态SQL生成的表变量,或者临时表.的使用域是有限的.

使用全局临时变量  ##
------解决方案--------------------

create table business

(id int,name varchar(10),pid int)



insert into business

select 1 id ,'中国' as name,null pid union all

select 2 id ,'江苏' as name,1 pid union all

select 3 id ,'山东' as name,1 pid union all

select 4 id ,'广东' as name,1 pid union all

select 5 id ,'南京' as name,2 pid union all

select 6 id ,'苏州' as name,2 pid union all

select 7 id ,'常州' as name,2 pid union all

select 8 id ,'济南' as name,3 pid union all

select 9 id ,'青岛' as name,3 pid union all

select 10 id ,'广州' as name,4 pid un