动态PIVOT 处理行列转换中数量未知的元素
Use TESTDB ;
Go
-- Initialization Start --
-- Create Table Orders & Judgment Table_Name Repetition --
IF OBJECT_ID('dbo.orders') IS NOT NULL
DROP TABLE dbo.orders;
GO
CREATE TABLE dbo.orders
(
orderid int NOT NULL PRIMARY KEY NONCLUSTERED,
orderdate datetime NOT NULL,
empid int NOT NULL,
custid varchar(5) NOT NULL,
qty int NOT NULL
);
-- Insert Orders Table Data --
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(30001,'20020802',3,'A',10);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(10001,'20021224',1,'A',12);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(10005,'20021224',1,'B',20);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(40001,'20030109',4,'A',40);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(10006,'20030108',1,'C',14);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(20001,'20030212',2,'B',12);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(40005,'20040212',4,'A',10);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(20002,'20040216',2,'C',20);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(30003,'20040418',3,'B',15);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(30004,'20020418',3,'C',22);
INSERT INTO dbo.orders(orderid,orderdate,empid,custid,qty)
VALUES(30007,'20020917',3,'D',30);
GO
-- 插入数据以后表的内容如下
-- select * from orders
-- Inquire Result:
-- orderid orderdate empid custid qty
-- 30001 2002-08-02 00:00:00.000 3 A 10
-- 10001 2002-12-24 00:00:00.000 1 A 12
-- 10005 2002-12-24 00:00:00.000 1 B 20
-- 40001 2003-01-09 00:00:00.000 4 A 40
-- 10006 2003-01-08 00:00:00.000 1 C 14
-- 20001 2003-02-12 00:00:00.000 2 B 12
-- 40005 2004-02-12 00:00:00.000 4 A 10
-- 20002 2004-02-16 00:00:00.000 2 C 20
-- 30003 2004-04-18 00:00:00.000 3 B 15
-- 30004 2002-04-18 00:00:00.000 3 C 22
-- 30007 2002-09-17 00:00:00.000 3 D 30
-- Initialization Over! --
---- 案例要求:
---- 需要查询并返回每个消费者custid 每年orderdate 的订单orderid 合计数量qty
---- 每个消费者是一行，每一年是一列
-- IF @tmp_step = xx 是基于多案例演示需要而定义
--
-- @tmp_step 将赋值为 'A','B','C','D'
-- A: 执行SQL 2000的静态方法
-- B：执行SQL 2000的动态方法
-- C: 执行SQL 2005的静态方法
-- D: 执行SQL 2005的动态方法，这里重点是是为了处理数量未知元素的行列转换
Declare @tmp_step varchar(1);
-- 例如想执行类型SQL 2005风格的静态方法
SET @tmp_step = 'C' ;
-- 下面的代码中有 N'’ 表示方法
-- 个人理解是 动态语句中存在空格、逗号和非字母类型的字符时需要使用
-- 加上 N 代表存入数据库时以 Unicode 格式存储
-- Unicode 数据中的每个字符都使用两个字节进行存储
-- 字符数据中的每个字符则都使用一个字节进行存储
-- Public Define Area Begin --
-- 建立 @T 表的目的是一个技巧
-- 先把不重复的年份列表保存在表变量 @T 中
-- 然后遍历该变量
-- 并基于此动态地构建一系列的CASE表达式
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS Nvarchar(MAX),
@y AS INT,
@sql AS Nvarchar(MAX) ;
-- Public Define Area End --
IF @tmp_step = 'A'
BEGIN
/*************************************************************************/
-- SQL 2000兼容写法 --
/*************************************************************************/
-- Example: Static PIVOT Inquire --
-- 静态写法，后续的动态写法就是为了构建和这里一样的语句
SELECT custid,
SUM(CASE WHEN orderyear = 2002 THEN qty END) as [2002],
SUM(CASE WHEN orderyear = 2003 THEN qty END) as [2003],
SUM(CASE WHEN orderyear = 2004 THEN qty END) as [2004]
FROM
(
SELECT custid,YEAR(orderdate) AS Orderyear,qty
FROM dbo.orders
) AS D
GROUP BY custid;
RETURN
END
IF @tmp_step = 'B'
BEGIN
-- Example: Dynamic PIVOT Inquire --
-- 用不重复的年份列表（结果列）填充 @T
INSERT INTO @T
SELECT DISTINCT YEAR(orderdate) FROM dbo.orders;
-- 构造用于 SELECT 子句的表达式列表
SET @y = (SELECT MIN(y) FROM @T);
SET @cols = N' ';
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols
+ N',' + NCHAR(13) + NCHAR(10)
+ N' SUM(CASE WHEN orderyear = '
+ CAST(@y AS NVARCHAR(4))
+ N' THEN qty END) AS '
+ QUOTENAME(@y) ;
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y) ;
END
SET @cols = SUBSTRING(@cols,2,LEN(@cols));
-- 构造完整的 T-SQL 语句并动态执行
SET @sql = N'SELECT custid' + @cols
+ N'FROM
(
SELECT custid,YEAR(orderdate) AS orderyear,qty
FROM dbo.orders
) AS D
GROUP BY custid;' ;
-- Debug Area;
-- PRINT @sql;
EXEC SP_executesql @sql;
RETURN
END
/*************************************************************************/
-- SQL 2005兼容写法 --
/*************************************************************************/
IF @tmp_step = 'C'
BEGIN
-- Example: Static PIVOT Inquire 静态写法 --
SELECT *
FROM
(
SELECT custid,YEAR(orderdate) AS orderyear,qty
FROM dbo.orders
) AS D
PIVOT
(
SUM(qty) FOR orderyear in([2002],[2003],[2004])
) AS P
RETURN
END
IF @tmp_step = 'D'
BEGIN
-- Example: Dynamic PIVOT Inquire 动态写法 --
-- 构造用于IN子句的列列表
-- 例如 [2002],[2003],[2004]
SET @cols = STUFF(
(
SELECT N',' + QUOTENAME(y) AS [text()]
FROM(SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.orders) AS Y
ORDER BY y
FOR XML PATH('')
),1,1,N''
);
-- stuff函数应该是：Stuff(',[2002],[2003],[2004]',1,1,'')，这里将内容里的第一个逗号去掉了
-- 而@cols 的内容应该是 [2002],[2003],[2004]
-- 需要注意 FOR XML PATH('')的用法
-- quotename函数应该是：使函数中的输入成为一个有效的标识符，
-- 例如行列转换中，结果有空格或者其它sql不能识别的字符，那在执行动态sql时会报错
-- 而quotename就是使这些不能正常识别的字符变为一个有效的标识符
-- 构造完整的 T-SQL 语句并动态执行
SET @sql = N'
SELECT *
FROM (
SELECT custid,YEAR(orderdate) AS orderyear,qty
FROM dbo.orders
) AS D
PIVOT (
SUM(qty) FOR orderyear IN (' + @cols + N')
) AS p;
'; -- 这里的单引号对应上面 N'
exec sp_executesql @sql ; -- 调用系统功能，执行动态@sql内的语句
RETURN
END
/***************************************************************************/
-- 以下部分作为上面一些特定函数的解释
-- sql STUFF用法
-- 1、作用
-- 删除指定长度的字符，并在指定的起点处插入另一组字符。
--
-- 2、语法
-- STUFF ( character_expression , start , length ,character_expression )
--
-- 3、示例
-- 以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串
-- SELECT STUFF('abcdef', 2, 3, 'ijklmn')
-- GO
-- 下面是结果集（abcdef的第二个字符开始连续3个字符bcd 被删除，然后插入lijklmn
-- aijklmnef
--
-- 4、参数
-- character_expression
-- 一个字符数据表达式。character_expression 可以是常量、变量，也可以是字符列或二进制数据列。
-- start
-- 一个整数值，指定删除和插入的开始位置。如果 start 或 length 为负，则返回空字符串。如果 start 比第一个 character_expression长，则返回空字符串。start 可以是 bigint 类型。
-- length
-- 一个整数，指定要删除的字符数。如果 length 比第一个 character_expression长，则最多删除到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。
--
-- 5、返回类型
-- 如果 character_expression 是受支持的字符数据类型，则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型，则返回二进制数据。
--
-- 6、备注
-- 如果结果值大于返回类型支持的最大值，则产生错误。
