select
case when 列1=1 then 列2 end [1],
case when 列1=2 then 列2 end [2],
case when 列1=3 then 列2 end [3]
from 表
case when 列1=1 then 列2 end [1],
case when 列1=2 then 列2 end [2],
case when 列1=3 then 列2 end [3]
from 表
解决方案 »
- sql server在远程还原时报错,请大家帮忙
- 如何获取 SQL Server Batch 列表
- 怎么在一个字段里再添加一个符号?
- 一段简单的SQL语句
- sqlserver行加锁的问题
- 请教几个问题,希望各位大大帮小弟一下
- UNION ALL 视图 'tb' 不可更新,因为没有找到分区列
- 执行语句出现一个错误,请大家看看
- 登陆密码经过MD5加密后存入SQLSERVER字段,如何保证不能直接打开数据库去编辑该字段值?
- 请问大家,这个SQL语句应该怎么写啊?
- <<SQL编程实用大全>>,<<VB.NET与SQL2000高级编程>>,多要买...想问数据库区的兄弟意见..听说SQL2003快出来勒,钱都花大2000上勒对不?穷学生
- 我现在要重装系统!请问如何备份和恢复我的SQL SVR中的数据库
create table tb(id int,value int)
insert into tb(id,value) values(1,10)
insert into tb(id,value) values(2,110)
insert into tb(id,value) values(3,120)
insert into tb(id,value) values(4,140)
insert into tb(id,value) values(5,160)
insert into tb(id,value) values(6,170)
insert into tb(id,value) values(7,104)declare @sql varchar(2000),@fdname varchar(10)
select @sql=''
declare #aa cursor for select distinct cast(id as varchar) from tb
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when id=' +@fdname+' then value end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select '+right(@sql,len(@sql)-1)+' from tb'
print @sql
exec(@sql)
rollback tran
create procedure aa
as
set nocount on
declare @sql varchar(2000),@fdname varchar(10)
select @sql=''
declare #aa cursor for select distinct cast(id as varchar) from tb
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when id=' +@fdname+' then value end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select '+right(@sql,len(@sql)-1)+' from tb'
print @sql
exec(@sql)
set nocount off然后在VB中,调用aa这个存储过程,就可以了.
你这个代码的排序是这样的:
1 10 11 12 2 3 4 5 6 7 8 9 ...
可不可以改为如下排序
1 2 3 4 5 6 7 8 9 10 11 ...
create table tb(id int,value int)
insert into tb(id,value) values(1,10)
insert into tb(id,value) values(2,110)
insert into tb(id,value) values(3,120)
insert into tb(id,value) values(4,140)
insert into tb(id,value) values(5,160)
insert into tb(id,value) values(6,170)
insert into tb(id,value) values(7,104)
insert into tb(id,value) values(8,124)
insert into tb(id,value) values(9,114)
insert into tb(id,value) values(10,104)
insert into tb(id,value) values(11,123)
insert into tb(id,value) values(12,134)
insert into tb(id,value) values(13,121)
insert into tb(id,value) values(14,100)declare @sql varchar(2000),@fdname varchar(10)
select @sql=''
declare #aa cursor for select distinct id from tb
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when id=' +cast(@fdname as varchar)+' then value end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select '+right(@sql,len(@sql)-1)+' from tb'
print @sql
exec(@sql)
rollback tran
我一般都用 pengdali(大力 V2.0) 的方法!
不知道还有没其他办法!(仅限SQL处理)