大力的方法仅能处理源数据结构明确的情况,而不能做成一个公用的过程。
我做了个通用的交叉过程,可以按如下方式提供参数:
@vSourceTAB As Varchar(2000), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名:如上注释段)
@vGroupbyField As Varchar(200), --被Selct Group By 要显示出来的,可以多个字段(记录可以有空值)
@vTransFormCol As Varchar(100), --交叉表中的合计等函数计算值的字段
@vFunction As Varchar(50), --默认值,交叉表中的函数,也可以是' 2*Sum'的计算公式
@vPivotCol As Varchar(50), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值)
@vStrWhere As Varchar(500) =Null, --Where 约束条件,可以为空
@vGroupFldType As Varchar(5)=Null --要转换成列的字段类型,如果是非数字型则不进行求和等运算
如果需要请Email: robin_f@sina.com
declare @sql varchar(8000)
set @sql = 'select 年份'
select @sql = @sql + ',sum(case 季度 when '''+cast(季度 as varchar)+''' then 数据 else 0 end) as '''+cast(季度 as varchar)+'季度数据'''
from (select distinct 季度 from 有一表) as a
select @sql = @sql+' from 有一表 group by 年份'
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
下面是用于创建旋转结果的 SELECT 语句:
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
该 SELECT 语句还处理其中每个季度占多行的表。GROUP BY 语句将 Pivot 中一年的所有行合并成一行输出。当执行分组操作时,SUM 聚合中的 CASE 函数的应用方式是这样的:将每季度的 Amount 值添加到结果集的适当列中,在其它季度的结果集列中添加 0。
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
带有 CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 都计算与本例显示相同的信息种类,但格式稍有不同