如何写个存储过程实现这样的效果？
表t1:
name col1 col2 col3
-----------------------------
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 0
b 11
b 12
b 13
b 14
b 15
b 16
b 17
b 18
b 19
b 10
如何写个存储过程将表转化成
name col1 col2 col3
--------------------------
a 1 4 8
a 2 5 9
a 3 6 0
a 7
b 11 15 18
b 12 16 19
b 13 17 10
b 14
问题点数：0、回复次数：12Top
1 楼bqb（萧雨）回复于 2005-06-03 13:42:37 得分 0
create table t1(name varchar(20),
col1 varchar(2),
col2 varchar(2),
col3 varchar(2))
insert into t1(name,col1,col2,col3)
select 'a','1','',''
union all
select 'a','2','',''
union all
select 'a','3','',''
union all
select 'a','','4',''
union all
select 'a','','5',''
union all
select 'a','','6',''
union all
select 'a','','7',''
union all
select 'a','','','8'
union all
select 'a','','','9'
union all
select 'a','','','0'
union all
select 'b','11','',''
union all
select 'b','12','',''
union all
select 'b','13','',''
union all
select 'b','14','',''
union all
select 'b','','15',''
union all
select 'b','','16',''
union all
select 'b','','17',''
union all
select 'b','','','18'
union all
select 'b','','','19'
union all
select 'b','','','10'
*/Top
2 楼xluzhong（Ralph）回复于 2005-06-03 14:15:26 得分 0
create table t1(name varchar(20),
col1 varchar(2),
col2 varchar(2),
col3 varchar(2))
insert into t1(name,col1,col2,col3)
select 'a','1','',''
union all
select 'a','2','',''
union all
select 'a','3','',''
union all
select 'a','','4',''
union all
select 'a','','5',''
union all
select 'a','','6',''
union all
select 'a','','7',''
union all
select 'a','','','8'
union all
select 'a','','','9'
union all
select 'a','','','0'
union all
select 'b','11','',''
union all
select 'b','12','',''
union all
select 'b','13','',''
union all
select 'b','14','',''
union all
select 'b','','15',''
union all
select 'b','','16',''
union all
select 'b','','17',''
union all
select 'b','','','18'
union all
select 'b','','','19'
union all
select 'b','','','10'
select * into #t from (select name,col1,sid=(select count(*) from t1 where name=a.name and col1<=a.col1 and col1>0) from t1 a where col1>0)t
select * into #t1 from (select name,col2,sid=(select count(*) from t1 where name=a.name and col2<=a.col2 and col2>0) from t1 a where col2>0)t
select * into #t2 from (select name,col3,sid=(select count(*) from t1 where name=a.name and col3<=a.col3 and col3>0) from t1 a where col3>0)t
select isnull(isnull(a.name,b.name),c.name) as name,
isnull(a.col1,'') col1,
isnull(b.col2,'') col2,
isnull(c.col3,'') col3
from #t a
full join #t1 b
on a.name=b.name and a.sid=b.sid
full join #t2 c
on a.name=c.name and a.sid=c.sid
drop table t1,#t,#t1,#t2
/*
name col1 col2 col3
--------------------------
a 1 4 8
a 2 5 9
a 3 6 0
a 7
b 11 15 18
b 12 16 19
b 13 17 10
b 14
*/
Top
3 楼bqb（萧雨）回复于 2005-06-03 17:46:32 得分 0
谢谢，xluzhong(Ralph) ( ) 信誉：100
我想要用动态变量，因为表t1 行不只有a,b,c .......； 列col1,col2,col3.......;
请大家帮我想想，谢谢！
Top
4 楼7271747（帅的拖网速）回复于 2005-06-03 17:55:06 得分 0
楼主是想打印报表吧，
如果是的话，建议你在报表机器上想想办法Top
5 楼Hopewell_Go（好的在后頭﹗希望更好﹗﹗）回复于 2005-06-03 20:47:31 得分 0
我想這個要調用系統表了。
syscolumns
Top
6 楼Hopewell_Go（好的在后頭﹗希望更好﹗﹗）回复于 2005-06-03 20:47:54 得分 0
還有一個sysobjectsTop
7 楼bqb（萧雨）回复于 2005-06-04 09:47:00 得分 0
还有没有人可以帮忙的呢！谢谢了！Top
8 楼xikboy（狼面书生）回复于 2005-06-04 10:21:29 得分 0
给me 分，不然砍人了Top
9 楼bqb（萧雨）回复于 2005-06-04 10:21:55 得分 0
有没有人可以帮忙！Top
10 楼chichunhua（無愧與心）回复于 2005-06-04 11:11:24 得分 0
幫你頂阿Top
11 楼hglhyy（為人民币服务！）回复于 2005-06-04 11:15:30 得分 0
好像发了两次 看另一帖老大 的跟帖Top
12 楼ejason（忏悔一生）回复于 2005-06-04 16:58:25 得分 0
有游标循环下得了Top
