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 都计算与本例显示相同的信息种类,但格式稍有不同。
drop table test_course
go
create table test_course (姓名 varchar(20),科目 varchar(20),成绩 int)
go
insert test_course values ('1','1',60)
insert test_course values ('1','2',60)
insert test_course values ('1','3',60)
insert test_course values ('1','4',60)
insert test_course values ('2','1',60)
insert test_course values ('2','3',60)
insert test_course values ('2','5',60)
insert test_course values ('2','6',60)
go
drop proc proc_course
go
create proc proc_course
as
begin
declare @str varchar(8000)
declare @cause varchar(20)
select @str = 'select 姓名, '
declare my_cursor cursor for
select distinct 科目 from test_course
open my_cursor
fetch next from my_cursor into @cause
while @@fetch_status = 0
begin
select @str = @str + 'sum((case 科目 when '''+ @cause + ' '' then 成绩 else 0 end)) 科目' + @cause + ','
fetch next from my_cursor
into @cause
end
close my_cursor
deallocate my_cursor
select @str= left(@str,len(@str)-1)
select @str= @str + ' from test_course group by 姓名'
exec( @str )
end
go