山寨机中的战斗机！	程序优化工程师到底对IT界有没有贡献

如何写个存储过程实现这样的效果？

楼主bqb（萧雨）2005-06-04 09:55:25 在 MS-SQL Server / 基础类提问

表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
问题点数：100、回复次数：11Top

1 楼bqb（萧雨）回复于 2005-06-04 09:58:10 得分 0

2 楼zjcxc（邹建）回复于 2005-06-04 10:11:58 得分 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'
go

select name=isnull(a.name,isnull(b.name,c.name)),
col1=isnull(a.col1,''),
col2=isnull(b.col2,''),
col3=isnull(c.col3,'')
from(
select name,col1,
id=(select count(*) from t1
where name=a.name and col1>'' and col1<=a.col1)
from t1 a
where col1>''
)a
full join(
select name,col2,
id=(select count(*) from t1
where name=a.name and col2>'' and col2<=a.col2)
from t1 a
where col2>''
)b on a.name=b.name and a.id=b.id
full join(
select name,col3,
id=(select count(*) from t1
where name=a.name and col3>'' and col3<=a.col3)
from t1 a
where col3>''
)c on a.name=c.name and a.id=c.id
go

--删除测试
drop table t1

/*--结果

name col1 col2 col3
-------------------- ---- ---- ----
a 1 4 0
a 2 5 8
a 3 6 9
a 7
b 11 15 10
b 12 16 18
b 13 17 19
b 14

（所影响的行数为 8 行）
--*/Top

3 楼bqb（萧雨）回复于 2005-06-04 10:18:18 得分 0

行和列有很多啊，a,b,c,d......... col1,col2,col3,col4.......
要用变量！Top

4 楼bqb（萧雨）回复于 2005-06-04 10:20:40 得分 0

谢谢！ zjcxc(邹建)
能用变量写看看吗？Top

5 楼yuncai（BtMan）回复于 2005-06-04 10:21:44 得分 5

领分了……现场解决
大家不要眼红哦！！Top

6 楼hglhyy（為人民币服务！）回复于 2005-06-04 10:23:21 得分 0

老大的思路真是好！Top

7 楼yjdn（人形机器）回复于 2005-06-04 10:24:45 得分 5

a 1 4 8
a 2 5 9
a 3 6 0
--
a 1 4 0
a 2 5 8
a 3 6 9

--跟楼主的有出入，邹老大是根据colN从小到大来安排序号的，问题出在9到0这里

--如果楼主把0改为10应该没这种事了，Top

8 楼hglhyy（為人民币服务！）回复于 2005-06-04 10:54:36 得分 5

我也做了个简单的，虽能达到目的，不是动态的

create table t(name varchar(20),
col1 varchar(2),
col2 varchar(2),
col3 varchar(2))

insert into t(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 identity(int,1,1) as id,name,col1 into a1 from T where name='a' and col1<>''
select identity(int,1,1) as id,name,col2 into a2 from T where name='a' and col2<>''
select identity(int,1,1) as id,name,col3 into a3 from T where name='a' and col3<>''

select identity(int,1,1) as id,name,col1 into b1 from T where name='b' and col1<>''
select identity(int,1,1) as id,name,col2 into b2 from T where name='b' and col2<>''
select identity(int,1,1) as id,name,col3 into b3 from T where name='b' and col3<>''

select b.name,a.col1,b.col2,c.col3 from a1 a,a2 b,a3 c where b.id*=c.id and b.id*=a.id
union

select a.name,a.col1,b.col2,c.col3 from b1 a,b2 b,b3 c where a.id*=b.id and a.id*=c.id

/*
drop table a1
drop table a2
drop table a3

drop table b1
drop table b2
drop table b3

*/

--结果

name col1 col2 col3
-------------------- ---- ---- ----
a NULL 7 NULL
a 1 4 8
a 2 5 9
a 3 6 0
b 11 15 18
b 12 16 19
b 13 17 10
b 14 NULL NULL

（所影响的行数为 8 行）

Top

9 楼chichunhua（無愧與心）回复于 2005-06-04 11:10:26 得分 5

markTop

10 楼zjcxc（邹建）回复于 2005-06-04 11:20:11 得分 80

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'
go

declare @s varchar(8000),@i int,@fd varchar(8000)
select @s='',@i=97,@fd=''
select @i=@i+1,
@s=@s+'
full join(
select name,'+rtrim(name)+',
id=(select count(*) from t1
where name=a.name and '+rtrim(name)+'>'''' and '+rtrim(name)+'<=a.'+rtrim(name)+')
from t1 a
where '+rtrim(name)+'>''''
)'+char(@i)+' on a.name='+char(@i)+'.name and a.id='+char(@i)+'.id',
@fd=@fd+',
'+rtrim(name)+'=isnull('+char(@i)+'.'+rtrim(name)+','''')'
from syscolumns
where id=object_id(N't1') and name<>'col1' and name like 'col%'
exec('
select name=isnull(a.name,isnull(b.name,c.name)),
col1=isnull(a.col1,'''')'+@fd+'
from(
select name,col1,
id=(select count(*) from t1
where name=a.name and col1>'''' and col1<=a.col1)
from t1 a
where col1>''''
)a'+@s)
go

--删除测试
drop table t1

/*--结果

name col1 col2 col3
-------------------- ---- ---- ----
a 1 4 0
a 2 5 8
a 3 6 9
a 7
b 11 15 10
b 12 16 18
b 13 17 19
b 14

（所影响的行数为 8 行）
--*/
Top

11 楼zjcxc（邹建）回复于 2005-06-04 11:20:46 得分 0

如上,就是动态的了.Top