爷儿俩结构数据的一种转转置

2013-07-01

父子结构数据的一种转转置本帖最后由 yiyishuitian 于 2013-05-06 09:11:33 编辑with business as (select

父子结构数据的一种转转置
本帖最后由 yiyishuitian 于 2013-05-06 09:11:33 编辑



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层.

idlev0lev1lev2lev3lev4
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 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

if object_id('tempdb..#t') is not null
  drop table #t
go

;with t as
(select id,name,pid,1 lv from business
union all
select a.id,b.name,b.pid,a.lv+1
  from t a
  inner join business b on a.pid=b.id
)
select id,name,lv into #t from t;

declare @tsql varchar(6000),@ls1 varchar(200),@ls2 varchar(200)

select @ls1=isnull(@ls1,'')+'isnull(['+rtrim(number)+'],'''') lev'+rtrim(number-1)+',',
       @ls2=isnull(@ls2,'')+'['+rtrim(number)+'],'
from master.dbo.spt_values
where type='P' and number between 1 and (select max(lv) from #t)

select @tsql='select id,'+left(@ls1,len(@ls1)-1)
             +' from (select id,name,row_number() over(partition by id order by lv desc) lv '
             +' from #t) c '
             +' pivot(max(name) for lv in('+left(@ls2,len(@ls2)-1)+')) p '

exec(@tsql)

/*
id          lev0       lev1       lev2       lev3       lev4
----------- ---------- ---------- ---------- ---------- ----------
1           中国
2           中国         江苏
3           中国         山东
4           中国         广东
5           中国         江苏         南京
6           中国         江苏         苏州
7           中国         江苏         常州

8           中国         山东         济南
9           中国         山东         青岛
10          中国         广东         广州
11          中国         广东         深圳
12          中国         江苏         南京         六合
13          中国         江苏         南京         六合         龙池

(13 row(s) affected)
*/

热点排行

SQL Server

爷儿俩结构数据的一种转转置