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
USE pubs-----------------------------------------------------------打开
DECLARE tnames_cursor CURSOR---------------------------------------定义游标
FOR----------------------------------------------------------------选择
SELECT TABLE_NAME ----------------------------------------------游标体
FROM INFORMATION_SCHEMA.TABLES----------------------------------表名
OPEN tnames_cursor-------------------------------------------------打开游标
DECLARE @tablename sysname-----------------------------------------
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename----------------------调用游标赋值
WHILE (@@FETCH_STATUS <> -1)---------------------------------------循环‘0’有‘-1’没找到‘-2’超出范围溢出
BEGIN
IF (@@FETCH_STATUS <> -2)---------------------------------------如果没有溢出
BEGIN
SELECT @tablename = RTRIM(@tablename)------------------------
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
declare @strsql varchar(8000)
declare @temp_date datetime
set @strsql='select ID'
declare #cr_date cursor for select distinct 日期字段 from
考勤数据记录表 where (日期字段)=month(getdate())
open #cr_date
fetch next from #cr_date into @temp_date
while @@fetch_status=0
begin
set @strsql=@strsql+
N',sum(case when 日期字段=''+
@temp_date+ N'''+
'then 时间字段 else 0 end) as '
+@temp_date
fetch next from #cr_date into @temp_date
end
close #cr_date
deallocate #cr_date
set @strsql=@strsql+' from 考勤数据记录表 group by 工號(ID)'
exec(@strsql)