表A结构如下:
ID Current Pay AllFee
001 RMB 应收 300
001 RMB 应收 100
001 USD 应收 50
001 USD 应付 100
002 RMB 应付 200
002 RMB 应收 100
003
…………………………想要得到如下结果:ID 应收RMB 应付RMB 应收USD 应付USD
001 400 0 50 100
002 100 200 0 0
…………………………
请指点一下,这个SQL语句怎么写???关注………………
ID Current Pay AllFee
001 RMB 应收 300
001 RMB 应收 100
001 USD 应收 50
001 USD 应付 100
002 RMB 应付 200
002 RMB 应收 100
003
…………………………想要得到如下结果:ID 应收RMB 应付RMB 应收USD 应付USD
001 400 0 50 100
002 100 200 0 0
…………………………
请指点一下,这个SQL语句怎么写???关注………………
create table #PP
(
ID nchar(10),
[Current] nchar(5),
Pay nvarchar(10),
AllFee int
)
insert into #PP select '001','RMB','应收',300
insert into #PP select '001','RMB','应收',100
insert into #PP select '001','USD','应收',50
insert into #PP select '001','USD','应付',100
insert into #PP select '002','RMB','应付',200
insert into #PP select '002','RMB','应收',100
select ID,
sum(case when [Current]='RMB' and Pay='应收' then AllFee else 0 end) '应收RMB ',
sum(case when [Current]='RMB' and Pay='应付' then AllFee else 0 end) '应付RMB ',
sum(case when [Current]='USD' and Pay='应收' then AllFee else 0 end) '应收USD ',
sum(case when [Current]='USD' and Pay='应付' then AllFee else 0 end) '应付USD '
from #PP
group by ID
create table #PP
(
ID nchar(10),
[Current] nchar(5),
Pay nvarchar(10),
AllFee int
)
insert into #PP select '001','RMB','应收',300
insert into #PP select '001','RMB','应收',100
insert into #PP select '001','USD','应收',50
insert into #PP select '001','USD','应付',100
insert into #PP select '002','RMB','应付',200
insert into #PP select '002','RMB','应收',100
select ID,
sum(case when [Current]='RMB' and Pay='应收' then AllFee else 0 end) '应收RMB ',
sum(case when [Current]='RMB' and Pay='应付' then AllFee else 0 end) '应付RMB ',
sum(case when [Current]='USD' and Pay='应收' then AllFee else 0 end) '应收USD ',
sum(case when [Current]='USD' and Pay='应付' then AllFee else 0 end) '应付USD '
from #PP
group by ID
ID 应收RMB 应付RMB 应收USD 应付USD
---------- ----------- ----------- ----------- -----------
001 400 0 50 100
002 100 200 0 0(2 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-02 16:22:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(3),[Current] varchar(3),[Pay] varchar(4),[AllFee] int)
insert [tb]
select '001','RMB','应收',300 union all
select '001','RMB','应收',100 union all
select '001','USD','应收',50 union all
select '001','USD','应付',100 union all
select '002','RMB','应付',200 union all
select '002','RMB','应收',100
--------------开始查询--------------------------
select
id,
max(case when [Current]='RMB' and Pay='应收' then AllFee else 0 end) as 应收RMB,
max(case when [Current]='RMB' and Pay='应付' then AllFee else 0 end) as 应付RMB,
max(case when [Current]='USD' and Pay='应收' then AllFee else 0 end) as 应收USD,
max(case when [Current]='USD' and Pay='应付' then AllFee else 0 end) as 应付USD
from
[tb]
group by
id
----------------结果----------------------------
/* id 应收RMB 应付RMB 应收USD 应付USD
---- ----------- ----------- ----------- -----------
001 300 0 50 100
002 100 200 0 0(2 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-02 16:22:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(3),[Current] varchar(3),[Pay] varchar(4),[AllFee] int)
insert [tb]
select '001','RMB','应收',300 union all
select '001','RMB','应收',100 union all
select '001','USD','应收',50 union all
select '001','USD','应付',100 union all
select '002','RMB','应付',200 union all
select '002','RMB','应收',100
--------------开始查询--------------------------
select
id,
max(case when [Current]='RMB' and Pay='应收' then AllFee else 0 end) as 应收RMB,
max(case when [Current]='RMB' and Pay='应付' then AllFee else 0 end) as 应付RMB,
max(case when [Current]='USD' and Pay='应收' then AllFee else 0 end) as 应收USD,
max(case when [Current]='USD' and Pay='应付' then AllFee else 0 end) as 应付USD
from
[tb]
group by
id
--动态
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case when [Current]= ''' + [Current] + ''' and Pay=''' + [Pay] + '''then AllFee else 0 end) [' + [Pay]+[Current] + ']'
from (select distinct [Current],pay from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
----------------结果----------------------------
/* id 应收RMB 应付RMB 应收USD 应付USD
---- ----------- ----------- ----------- -----------
001 300 0 50 100
002 100 200 0 0(2 行受影响)*/