有一个表(charge),字段如下:bh(编号) bb(币别) je(金额)
001 usd 1000
001 usd 100
001 hkd 200
002 rmb 100
003 eup 400其中币别没有固定有几个,可以为多个个,想动态按编号与币别求各种币别的金额合计
结果为:编号 USD HKD RMB EUP ....... 001 1100 200
002 100
003 400币别是不固定的,请大家帮一下!谢谢!
001 usd 1000
001 usd 100
001 hkd 200
002 rmb 100
003 eup 400其中币别没有固定有几个,可以为多个个,想动态按编号与币别求各种币别的金额合计
结果为:编号 USD HKD RMB EUP ....... 001 1100 200
002 100
003 400币别是不固定的,请大家帮一下!谢谢!
INSERT INTO tb
SELECT '001', 'usd', 1000
UNION ALL SELECT '001', 'usd', 100
UNION ALL SELECT '001', 'hkd', 200
UNION ALL SELECT '002', 'rmb', 100
UNION ALL SELECT '003', 'eup', 400DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT bh,'SELECT @SQL = @SQL + '
SUM(CASE bb WHEN ''' + A.bb + ''' THEN je ELSE 0 END) AS [' + A.bb + '],'
FROM (SELECT DISTINCT bb FROM tb)A
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' FROM tb GROUP BY bh'
PRINT @SQL
EXEC(@SQL)DROP TABLE tb结果
bh eup hkd rmb usd
---------- ----------- ----------- ----------- -----------
001 0 200 0 1100
002 0 0 100 0
003 400 0 0 0
insert into TableName
select '001', 'usd', 1000
union all
select '001', 'usd', 100
union all
select '001', 'hkd', 200
union all
select '002', 'rmb' , 100
union all
select '003', 'eup', 400
declare @strSQL varchar(8000)
set @strSQL=''
select @strSQL=@strSQL+',sum(case when bb='''+bb+''' then je else 0 end ) as '+bb
from (select distinct bb from TableName)T
set @strSQL='select bh'+@strSQL+' from TableName group by bh'
exec(@strSQL)
结果:
bh eup hkd rmb usd
--------------------------------------------
001 .0000 200.0000 .0000 1100.0000
002 .0000 .0000 100.0000 .0000
003 400.0000 .0000 .0000 .0000
if object_id('pubs..charge') is not null
drop table charge
go
create table charge
(
bh varchar(10),
bb varchar(10),
je int
)
insert into charge(bh,bb,je) values('001','usd',1000)
insert into charge(bh,bb,je) values('001','usd',100)
insert into charge(bh,bb,je) values('001','hkd',200)
insert into charge(bh,bb,je) values('002','rmb',100)
insert into charge(bh,bb,je) values('003','eup',4000)
go
--语句
declare @sql varchar(8000)
set @sql = 'select bh as ' + '编号'
select @sql = @sql + ' , sum(case bb when ''' + bb + ''' then je else 0 end) [' + upper(bb) + ']'
from (select distinct bb from charge) as a
set @sql = @sql + ' from charge group by bh'
exec(@sql)
--删除表
drop table charge--结果
编号 EUP HKD RMB USD
---------- ----------- ----------- ----------- -----------
001 0 200 0 1100
002 0 0 100 0
003 4000 0 0 0
应该为400,输入错误.不好意思.
-----------------------------------------------------------
--建立数据源
if object_id('pubs..charge') is not null
drop table charge
go
create table charge
(
bh varchar(10),
bb varchar(10),
je int
)
insert into charge(bh,bb,je) values('001','usd',1000)
insert into charge(bh,bb,je) values('001','usd',100)
insert into charge(bh,bb,je) values('001','hkd',200)
insert into charge(bh,bb,je) values('002','rmb',100)
insert into charge(bh,bb,je) values('003','eup',400)
go
--语句
declare @sql varchar(8000)
set @sql = 'select bh as ' + '编号'
select @sql = @sql + ' , sum(case bb when ''' + bb + ''' then je else 0 end) [' + upper(bb) + ']'
from (select distinct bb from charge) as a
set @sql = @sql + ' from charge group by bh'
exec(@sql)
--删除表
drop table charge--结果
编号 EUP HKD RMB USD
---------- ----------- ----------- ----------- -----------
001 0 200 0 1100
002 0 0 100 0
003 400 0 0 0
insert charge select '001', 'usd', 1000
union all select '001' , 'usd', 100
union all select '001', 'hkd', 200
union all select '002', 'rmb', 100
union all select '003', 'eup', 400declare @str nvarchar(4000)
set @str=' select bh, 'select @str=@str+quotename(bb)+'=case when bh='''+bh+''' then '+cast(sum(je) as nvarchar)+' else null end, ' from charge group by bh, bb
select @str=left(@str, len(@str)-1), @str=@str+' from charge group by bh '
exec(@str)drop table charge--result
bh eup hkd rmb usd
---- ----------- ----------- ----------- -----------
001 NULL 200 NULL 1100
002 NULL NULL 100 NULL
003 400 NULL NULL NULL
insert @t select '001','usd',1000
insert @t select '001','usd',100
insert @t select '001','hkd',200
insert @t select '002','rmb',100
insert @t select '003','eup',400DECLARE @SQL VARCHAR(8000)
select @Sql='select bh'
select @sql=@sql+',sum(case bb when '''+a.bb+'''then je else 0 end) as ['+a.bb+']' from
(select distinct bb from @t) aselect @sql=@sql+ ' from @t group by bh '
print @sqlselect bh,sum(case bb when 'eup'then je else 0 end) as [eup],sum(case bb when 'hkd'then je else 0 end) as [hkd],sum(case bb when 'rmb'then je else 0 end) as [rmb],sum(case bb when 'usd'then je else 0 end) as [usd] from @t group by bh
*************************
bh eud hkd rmb usd
001 0 200 0 1100
002 0 0 100 0
003 400 0 0 0
select bh,sum(case bb when 'eup'then je else 0 end) as [eup],sum(case bb when 'hkd'then je else 0 end) as [hkd],sum(case bb when 'rmb'then je else 0 end) as [rmb],sum(case bb when 'usd'then je else 0 end) as [usd] from @t group by bh 最后一句这样做不是动态的