现有数据表内容如下:country Org Price
巴西 北京 200
美国 北京 300
泰国 北京 400
巴西 广东 250
法国 广东 350
美国 上海 300
法国 上海 400希望得到如下交叉表统计结果: 小计 巴西 美国 泰国 法国
北京 900 200 300 400 0
广东 600 250 0 0 350
上海 700 0 300 0 400查询了过去的贴子,发现没有哪个能适用的(或者我不知道如何适用)有几点要说明的:
1、表中的巴西、美国等国家不是固定值,随时可能有新的国家增加或减少。
2、表中的省份也不是固定值。
巴西 北京 200
美国 北京 300
泰国 北京 400
巴西 广东 250
法国 广东 350
美国 上海 300
法国 上海 400希望得到如下交叉表统计结果: 小计 巴西 美国 泰国 法国
北京 900 200 300 400 0
广东 600 250 0 0 350
上海 700 0 300 0 400查询了过去的贴子,发现没有哪个能适用的(或者我不知道如何适用)有几点要说明的:
1、表中的巴西、美国等国家不是固定值,随时可能有新的国家增加或减少。
2、表中的省份也不是固定值。
http://hi.baidu.com/qzrc/blog/item/952686184634e0b44aedbcea.html
http://www.cppblog.com/lilac/archive/2008/01/13/41069.html
http://topic.csdn.net/u/20080613/17/410d30b4-e25c-492f-8df0-118a45dd1a34.html
http://dev.firnow.com/course/7_databases/sql/sqlServer/2008126/97611.htmlhttp://www.jb51.net/article/19812.htm
(
SELECT ORG, ISNULL([巴西],0)[巴西],
ISNULL([美国],0)[美国],ISNULL([泰国],0)[泰国],
ISNULL([法国],0)[法国]
FROM #tp t
PIVOT(SUM(PRICE)FOR country IN([巴西],[美国],[泰国],[法国]))PVT
)m
LEFT JOIN
(SELECT org,SUM(price)小计 from #tp group by org)P
ON m.ORG=p.orgORG 小计 巴西 美国 泰国 法国
---------- ----------- ----------- ----------- ----------- -----------
上海 700 0 300 0 400
北京 900 200 300 400 0
广东 600 250 0 0 350(3 row(s) affected)
[Author]: OrchidCat[OC]_轻骑兵(向高手学习...)
[Time]: 2010-11-04 08:31:07
[Place]: From Beijing
[Version]:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([country] varchar(4),[Org] varchar(4),[Price] int)
insert [TB]
select '巴西','北京',200 union all
select '美国','北京',300 union all
select '泰国','北京',400 union all
select '巴西','广东',250 union all
select '法国','广东',350 union all
select '美国','上海',300 union all
select '法国','上海',400select * from [TB]
declare @sql varchar(8000)
set @sql = 'select org'
select @sql = @sql + ' , max(case country when ''' + country + ''' then price else 0 end) [' + country + ']'
from (select distinct country from tb) as a
set @sql = @sql + ' from tb group by org'
exec(@sql) /*
org 巴西 法国 美国 泰国
北京 200 0 300 400
广东 250 350 0 0
上海 0 400 300 0*/
SET @sql = 'select org,[小计]=sum(price)'
SELECT @sql = @sql + ' , max(case country when ''' + country
+ ''' then price else 0 end) [' + country + ']'
FROM ( SELECT DISTINCT
country
FROM tb
) AS a
SET @sql = @sql + ' from tb group by org'
EXEC(@sql) /*
org 小计 巴西 法国 美国 泰国
---- ----------- ----------- ----------- ----------- -----------
北京 900 200 0 300 400
广东 600 250 350 0 0
上海 700 0 400 300 0(3 行受影响)*/
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'select * into ##temp from (SELECT ORG '
SELECT @SQL = @SQL + N' , MAX(CASE COUNTRY WHEN N''' + COUNTRY + ''' THEN PRICE ELSE 0 END) [' + COUNTRY + ']'
FROM (SELECT DISTINCT COUNTRY FROM #TP) AS A
SET @SQL = @SQL + N' FROM #TP GROUP BY ORG)M'EXEC(@SQL) -----再进行连接小计栏位
SELECT m.*,p.[小计]
FROM ##TEMP M
LEFT JOIN
(SELECT org,SUM(price)小计 from #tp group by org)P
ON m.ORG=p.orgORG 小计 巴西 美国 泰国 法国
---------- ----------- ----------- ----------- ----------- -----------
上海 700 0 300 0 400
北京 900 200 300 400 0
广东 600 250 0 0 350(3 row(s) affected)
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'SELECT ORG,sum(price)小计 '
SELECT @SQL = @SQL + N' , MAX(CASE COUNTRY WHEN N''' + COUNTRY + ''' THEN PRICE ELSE 0 END) [' + COUNTRY + ']'
FROM (SELECT DISTINCT COUNTRY FROM #TP) AS A
SET @SQL = @SQL + N' FROM #TP GROUP BY ORG'EXEC(@SQL)ORG 小计 巴西 法国 泰国 美国
---------- ----------- ----------- ----------- ----------- -----------
上海 700 0 400 0 300
北京 900 200 0 400 300
广东 600 250 350 0 0(3 row(s) affected)
谢谢各位高手的指点!