ExchId Currency ToCurrency CurrencyRate Month
1 HK USD 0.78 3 2009
2 HK RMB 0.85 3 2009
3 HK RMB 0.86 4 2009
4 HK TWD 0.55 2 2009
5 HK MYA 0.66 1 2009
6 USD HK 0.33 2 2009
7 USD USD 1 3 2009
8 USD RMB 0.55 3 2009
9 USD TWD 0.66 3 2009
10 MYA HK 0.15 7 2008
11 MYA USD 0.88 7 2008
12 MYA SDG 0.77 8 2008
13 MYA RMB 0.99 8 2008
我要求变成这样一种交叉报表
CountryCurrency Month USD HK RMB TWD MYA SDG
HK 3 2009 0.78 1 0.85 0 0 0
HK 4 2009 0 1 0.86 0 0 0
HK 2 2009 0 1 0 0.55 0 0
USD 2 2009 1 0.33 0 0 0 0
USD 3 2009 1 0 0.55 0.66 0 0
MYA 7 2008 0.88 0.15 0 0 1 0
MYA 8 2008 0 0 0.99 0 1 0.77 请教各位如何实现? 谢谢了！
1 HK USD 0.78 3 2009
2 HK RMB 0.85 3 2009
3 HK RMB 0.86 4 2009
4 HK TWD 0.55 2 2009
5 HK MYA 0.66 1 2009
6 USD HK 0.33 2 2009
7 USD USD 1 3 2009
8 USD RMB 0.55 3 2009
9 USD TWD 0.66 3 2009
10 MYA HK 0.15 7 2008
11 MYA USD 0.88 7 2008
12 MYA SDG 0.77 8 2008
13 MYA RMB 0.99 8 2008
我要求变成这样一种交叉报表
CountryCurrency Month USD HK RMB TWD MYA SDG
HK 3 2009 0.78 1 0.85 0 0 0
HK 4 2009 0 1 0.86 0 0 0
HK 2 2009 0 1 0 0.55 0 0
USD 2 2009 1 0.33 0 0 0 0
USD 3 2009 1 0 0.55 0.66 0 0
MYA 7 2008 0.88 0.15 0 0 1 0
MYA 8 2008 0 0 0.99 0 1 0.77 请教各位如何实现? 谢谢了！
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ExchId INT,Currency VARCHAR(3),ToCurrency VARCHAR(3),CurrencyRate NUMERIC(3,2),Month VARCHAR(6))
INSERT INTO @T
SELECT 1,'HK','USD',0.78,'3_2009' UNION ALL
SELECT 2,'HK','RMB',0.85,'3_2009' UNION ALL
SELECT 3,'HK','RMB',0.86,'4_2009' UNION ALL
SELECT 4,'HK','TWD',0.55,'2_2009' UNION ALL
SELECT 5,'HK','MYA',0.66,'1_2009' UNION ALL
SELECT 6,'USD','HK',0.33,'2_2009' UNION ALL
SELECT 7,'USD','USD',1,'3_2009' UNION ALL
SELECT 8,'USD','RMB',0.55,'3_2009' UNION ALL
SELECT 9,'USD','TWD',0.66,'3_2009' UNION ALL
SELECT 10,'MYA','HK',0.15,'7_2008' UNION ALL
SELECT 11,'MYA','USD',0.88,'7_2008' UNION ALL
SELECT 12,'MYA','SDG',0.77,'8_2008' UNION ALL
SELECT 13,'MYA','RMB',0.99,'8_2008'--SQL查询如下:SELECT
Currency,
Month,
ISNULL(USD,0) AS USD,
ISNULL(HK,0) AS HK,
ISNULL(RMB,0) AS RMB,
ISNULL(TWD,0) AS TWD,
ISNULL(MYA,0) AS MYA,
ISNULL(SDG,0) AS SDG
FROM (
SELECT Currency,ToCurrency,CurrencyRate,Month
FROM @T
) AS A
PIVOT(
SUM(CurrencyRate) FOR ToCurrency IN(USD,HK,RMB,TWD,MYA,SDG)
) AS pvt
ORDER BY Currency,Month/*
Currency Month USD HK RMB TWD MYA SDG
-------- ------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
HK 1_2009 0.00 0.00 0.00 0.00 0.66 0.00
HK 2_2009 0.00 0.00 0.00 0.55 0.00 0.00
HK 3_2009 0.78 0.00 0.85 0.00 0.00 0.00
HK 4_2009 0.00 0.00 0.86 0.00 0.00 0.00
MYA 7_2008 0.88 0.15 0.00 0.00 0.00 0.00
MYA 8_2008 0.00 0.00 0.99 0.00 0.00 0.77
USD 2_2009 0.00 0.33 0.00 0.00 0.00 0.00
USD 3_2009 1.00 0.00 0.55 0.66 0.00 0.00(8 行受影响)
*/
INSERT INTO #1
SELECT 1,'HK','USD',0.78,'3_2009' UNION ALL
SELECT 2,'HK','RMB',0.85,'3_2009' UNION ALL
SELECT 3,'HK','RMB',0.86,'4_2009' UNION ALL
SELECT 4,'HK','TWD',0.55,'2_2009' UNION ALL
SELECT 5,'HK','MYA',0.66,'1_2009' UNION ALL
SELECT 6,'USD','HK',0.33,'2_2009' UNION ALL
SELECT 7,'USD','USD',1,'3_2009' UNION ALL
SELECT 8,'USD','RMB',0.55,'3_2009' UNION ALL
SELECT 9,'USD','TWD',0.66,'3_2009' UNION ALL
SELECT 10,'MYA','HK',0.15,'7_2008' UNION ALL
SELECT 11,'MYA','USD',0.88,'7_2008' UNION ALL
SELECT 12,'MYA','SDG',0.77,'8_2008' UNION ALL
SELECT 13,'MYA','RMB',0.99,'8_2008'
go
select Currency as CountryCurrency,Month
,MAX( case when Currency = 'USD' then 1 when ToCurrency = 'USD' then CurrencyRate else 0 end) USD
,MAX( case when Currency = 'HK' then 1 when ToCurrency = 'HK' then CurrencyRate else 0 end) HK
,MAX( case when Currency = 'RMB' then 1 when ToCurrency = 'RMB' then CurrencyRate else 0 end) RMB
,MAX( case when Currency = 'TWD' then 1 when ToCurrency = 'TWD' then CurrencyRate else 0 end) TWD
,MAX( case when Currency = 'MYA' then 1 when ToCurrency = 'MYA' then CurrencyRate else 0 end) MYA
,MAX( case when Currency = 'SDG' then 1 when ToCurrency = 'SDG' then CurrencyRate else 0 end) SDG
from #1
group by Currency,Month
order by Currency,Month
/*
CountryCurrency Month USD HK RMB TWD MYA SDG
--------------- ------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
HK 1_2009 0.00 1.00 0.00 0.00 0.66 0.00
HK 2_2009 0.00 1.00 0.00 0.55 0.00 0.00
HK 3_2009 0.78 1.00 0.85 0.00 0.00 0.00
HK 4_2009 0.00 1.00 0.86 0.00 0.00 0.00
MYA 7_2008 0.88 0.15 0.00 0.00 1.00 0.00
MYA 8_2008 0.00 0.00 0.99 0.00 1.00 0.77
USD 2_2009 1.00 0.33 0.00 0.00 0.00 0.00
USD 3_2009 1.00 0.00 0.55 0.66 0.00 0.00(8 row(s) affected)
*/
select @sql='select Currency ,Month '
select @sql=@sql+',sum( case ToCurrency when '''+ToCurrency+ ''' then CurrencyRate else 0 end) as '+ ToCurrency from tb group by ToCurrency
select @sql=@sql+' from tb group by Currency ,Month'
exec(@sql)
动态语句declare @sql varchar(max)
set @sql = 'select Currency as CountryCurrency,Month '
select @sql = @sql + ',Max(case when Currency = '''+ToCurrency+''' then 1 when ToCurrency = '''+ToCurrency+''' then CurrencyRate else 0 end) '+ToCurrency from
(select distinct ToCurrency from #1)t
set @sql = @sql +' from #1 group by Currency,Month order by Currency,Month '
exec(@sql)/*
CountryCurrency Month USD HK RMB TWD MYA SDG
--------------- ------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
HK 1_2009 0.00 1.00 0.00 0.00 0.66 0.00
HK 2_2009 0.00 1.00 0.00 0.55 0.00 0.00
HK 3_2009 0.78 1.00 0.85 0.00 0.00 0.00
HK 4_2009 0.00 1.00 0.86 0.00 0.00 0.00
MYA 7_2008 0.88 0.15 0.00 0.00 1.00 0.00
MYA 8_2008 0.00 0.00 0.99 0.00 1.00 0.77
USD 2_2009 1.00 0.33 0.00 0.00 0.00 0.00
USD 3_2009 1.00 0.00 0.55 0.66 0.00 0.00(8 row(s) affected)*/
INSERT INTO @T
SELECT 1,'HK','USD',0.78,'3_2009' UNION ALL
SELECT 2,'HK','RMB',0.85,'3_2009' UNION ALL
SELECT 3,'HK','RMB',0.86,'4_2009' UNION ALL
SELECT 4,'HK','TWD',0.55,'2_2009' UNION ALL
SELECT 5,'HK','MYA',0.66,'1_2009' UNION ALL
SELECT 6,'USD','HK',0.33,'2_2009' UNION ALL
SELECT 7,'USD','USD',1,'3_2009' UNION ALL
SELECT 8,'USD','RMB',0.55,'3_2009' UNION ALL
SELECT 9,'USD','TWD',0.66,'3_2009' UNION ALL
SELECT 10,'MYA','HK',0.15,'7_2008' UNION ALL
SELECT 11,'MYA','USD',0.88,'7_2008' UNION ALL
SELECT 12,'MYA','SDG',0.77,'8_2008' UNION ALL
SELECT 13,'MYA','RMB',0.99,'8_2008'--SQL查询如下:SELECT
Currency,
Month,
ISNULL(USD,0) AS USD,
ISNULL(HK,0) AS HK,
ISNULL(RMB,0) AS RMB,
ISNULL(TWD,0) AS TWD,
ISNULL(MYA,0) AS MYA,
ISNULL(SDG,0) AS SDG
FROM (
SELECT Currency,ToCurrency,CurrencyRate,Month
FROM @T
) AS A
PIVOT(
SUM(CurrencyRate) FOR ToCurrency IN(USD,HK,RMB,TWD,MYA,SDG)
) AS pvt
ORDER BY Currency,Month