请教一个复杂的SQL语句!

[bqj]

如下表的数据
1 10
2 15
3 16
...
31 13

如何用一个SQL语句提到如下结果
1 2 3 ... 31
10 15 16 ... 13

[liuyun2003]

gz

[yangzhangheng_jim]

看一下交叉表的用法

[pengdali]

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 表

[bqj]

还有其它办法吗？

[zjcxc]

begin tran
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

[zjcxc]

这个是动态生成的,你的数据增加,相应的结果变化.

[wynbfqny]

zjcxc(邹建) 能写个用vb的吗？

[jones129]

有,还在可用游标.然后用创建表和追加字段来把表转向

[zjcxc]

把它改成存储过程,然后在VB中调用这个存储过程就行了.
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这个存储过程,就可以了.

[wynbfqny]

谢谢 zjcxc(邹建)

[bqj]

zjcxc(邹建)
你这个代码的排序是这样的：
１　１０　１１　１２　２　３　４　５　６　７　８　９　...
可不可以改为如下排序
１　２　３　４　５　６　７　８　９　１０　１１　...

[bqj]

谢谢大家!

这样就可以了:

begin tran
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处理）