请问怎样利用SQL语句得到数据表中内容转置的结果？

[Taken]

我想将数据表中的内容转置后输出，请问如何得到这样的结果？记得以前有过这样的帖子，不知道怎么回事搜不到了，大家多帮忙啊！！！先谢过了！

[Taken]

自己先up一下

[txlicenhe]

Select ZH,
sum(case when JYDM = '9999' then JYJE else 0 end) as 9999交易总金额,
sum(case when JYDM = '9998' then JYJE else 0 end) as 9998交易总金额,
sum(case when JYDM = '9995' then JYJE else 0 end) as 9995交易总金额
from yourTable group by ZH


OR:

declare @sql varchar(8000)
set @sql = 'select ZH,'
select @sql = @sql + 'sum(case JYDM when '''+JYDM+'''
then JYJE else 0 end) '+JYDM+'交易总金额,'
from (select distinct JYDM from 你的表) as a

select @sql = left(@sql,len(@sql)-1) + ' from 你的表'

exec(@sql)

[zjcxc]

举个例子:
--1.产品表(CPB):
create table cpb(商品编号 varchar(1),名称 varchar(4),规格 varchar(6))
insert into cpb values('a','甲鱼','S-23')
insert into cpb values('b','鲤鱼','D-9')
insert into cpb values('c','鲇鱼','F-2')
insert into cpb values('d','带鱼','R-20')

go
--2.日记帐表(RJZ)
create table rjz(商品编号 varchar(1),时间 varchar(6),入库额 int,出库额 int)
insert into rjz values('a','星期一',10,9)
insert into rjz values('b','星期二',5,4)
insert into rjz values('c','星期一',10,9)
insert into rjz values('a','星期二',5,4)
insert into rjz values('b','星期一',10,9)
insert into rjz values('a','星期三',5,4)
insert into rjz values('a','星期五',5,0)

--要求结果:
/*
商品名 规格 星期一 星期二 星期三
入库 出库 入库 出库 入库 出库
甲鱼 S-23 10 9 10 9 5 4
鲤鱼 D-9 10 9 5 4 0 0
鲇鱼 F-2 5 4 0 0 0 0
*/
go
--用存储过程来实现
create procedure pbuild @start varchar(10),@end varchar(10)
as
declare @sql varchar(1000),@fd varchar(10)
declare @filter varchar(100),@fdname varchar(500),@i int

--得到要处理数据的表
select id=identity(int,1,1),name
into #temp
from
(select '星期一' as name
union all select '星期二'
union all select '星期三'
union all select '星期四'
union all select '星期五'
union all select '星期六'
union all select '星期日') a

declare #aa cursor for
select distinct 时间,id
from rjz a,#temp b
where a.时间=b.name
and b.id between
(select id from #temp where name=@start)
and
(select id from #temp where name=@end)
order by b.id

select @sql='select 商品编号 ',@filter='',@fdname=''
open #aa
fetch next from #aa into @fd,@i
while @@fetch_status=0
begin
select @filter=@filter+''''+@fd+''','
,@fdname=@fdname+',b.['+@fd+'-入],b.['+@fd+'-出]'
, @sql=@sql
+char(13)+' ,sum(case 时间 when '''+@fd
+''' then 入库额 else 0 end) as ['+@fd+'-入]'
+char(13)+' ,sum(case 时间 when '''
+@fd+''' then 出库额 else 0 end) as ['+@fd+'-出]'
fetch next from #aa into @fd,@i
end
close #aa
deallocate #aa
set @sql='select a.名称 as 商品名称,a.规格'+@fdname
+char(13)+' from cpb a,('
+@sql+char(13)+' from rjz'
+char(13)+' where 时间 in('
+left(@filter,len(@filter)-1)+')'
+char(13)+' group by 商品编号) b'
+char(13)+'where a.商品编号=b.商品编号'

--print @sql
exec(@sql)

go

--测试
exec pbuild '星期一','星期三'

go
--删除测试环境
drop table cpb,rjz
drop procedure pbuild

[zjcxc]

例子2:

create table 销售记录(流水号 int,商店 varchar(20), 商品名称 varchar(100), 销售额 money,日期 datetime)
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(1,'甲','苹果',100.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(2,'乙','苹果',10.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(3,'丙','梨',200.00,'2003-1-1')
insert into 销售记录(流水号,商店,商品名称,销售额,日期)
values(4,'丁','香蕉',300.00,'2003-1-1')
go
create procedure test
as
declare @sql varchar(2000),@fdname varchar(100)
select @sql=''
declare #aa cursor for select distinct 商品名称 from 销售记录
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when 商品名称=''' +@fdname+''' then 销售额 end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select 流水号,商店,'+right(@sql,len(@sql)-1)+',日期 from 销售记录 group by 流水号,商店,日期'
print @sql

exec(@sql)
go
exec test

drop table 销售记录
drop procedure test

[CrazyFor]

参考:
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)

declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a

select @sql = left(@sql,len(@sql)-1) + ' from #'

exec(@sql)

drop table #

[nboys]

create table #student
(
学生编号 int identity(1,1) primary key clustered,
姓名 varchar(50)
)

create table #league
(
社团编号 int identity(1,1) primary key clustered,
社团名称 varchar(80)
)

create table #leaguer
(
学生编号 int foreign key references dbo.#student([学生编号]) on delete cascade on update cascade,
社团编号 int foreign key references dbo.#league([社团编号]) on delete cascade on update cascade primary key (学生编号,社团编号)
)

insert into #student values('lxl')
insert into #student values('abc')
insert into #student values('88')
insert into #student values('99')

insert into #league values('体育协会')
insert into #league values('音乐协会')
insert into #league values('美术协会')
insert into #league values('科技协会')
insert into #league values('英语协会')
insert into #league values('计算机协会')

insert into #leaguer values(1,4)
insert into #leaguer values(3,2)
insert into #leaguer values(3,5)
insert into #leaguer values(1,2)
insert into #leaguer values(3,4)
insert into #leaguer values(1,3)
insert into #leaguer values(3,1)
insert into #leaguer values(2,1)
insert into #leaguer values(2,5)

declare @sql varchar(8000),@i int,@sql2 varchar(8000)
select @i=0,@sql = 'select 姓名',@sql2=''
select @i=@i+1,@sql = @sql + ',max(case 社团编号 when '''+cast(社团编号 as varchar(10))+''' then 社团名称 end) [社团名称'+cast(@i as varchar(10))+']'
,@sql2=@sql2+',cast(count(社团名称'+cast(@i as varchar(10))+') as varchar(100))'
from #league
select @sql = @sql+' into #a from (select a.姓名,b.社团编号,b.社团名称 from #student a,#league b,#leaguer c where a.学生编号=c.学生编号 and b.社团编号=c.社团编号) tem group by 姓名'

exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')

go
drop table #leaguer,#student,#league

[Taken]

解决了，谢谢大家了！：）

[CrazyFor]

参考:
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)

declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a

select @sql = left(@sql,len(@sql)-1) + ' from #'

exec(@sql)

drop table #