SQL 2005 tips --Pivot

USE tempdb

GO

CREATE TABLE AuctionItems

(

  itemid       INT          NOT NULL PRIMARY KEY NONCLUSTERED,

  itemtype     NVARCHAR(30) NOT NULL,

  whenmade     INT          NOT NULL,

  initialprice MONEY        NOT NULL,

  /* other columns */

)

--项目类型、项目的制造日期以及它的初始价格。只有与所有项目有关的属性被存储在AuctionItems

CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid

  ON AuctionItems(itemtype, itemid)

INSERT INTO AuctionItems VALUES(1, N'Wine',     1822,      3000)

INSERT INTO AuctionItems VALUES(2, N'Wine',     1807,       500)

INSERT INTO AuctionItems VALUES(3, N'Chair',    1753,    800000)

INSERT INTO AuctionItems VALUES(4, N'Ring',     -501,   1000000)

INSERT INTO AuctionItems VALUES(5, N'Painting', 1873,   8000000)

INSERT INTO AuctionItems VALUES(6, N'Painting', 1889,   8000000)

 

--其他属性特定于项目类型，并且不同类型的新项目被不断地添加。这样的属性可以存储在不同的ItemAttributes 表中，其中每个项属性都存储在不同的行中。每个行都包含项目ID、属性名称和属性值

CREATE TABLE ItemAttributes

(

  itemid    INT          NOT NULL REFERENCES AuctionItems,

 

  attribute NVARCHAR(30) NOT NULL,

  value     SQL_VARIANT  NOT NULL,

  PRIMARY KEY (itemid, attribute)

)

INSERT INTO ItemAttributes

  VALUES(1, N'manufacturer', CAST(N'ABC'              AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(1, N'type',         CAST(N'Pinot Noir'       AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(1, N'color',        CAST(N'Red'              AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(2, N'manufacturer', CAST(N'XYZ'              AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(2, N'type',         CAST(N'Porto'            AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(2, N'color',        CAST(N'Red'              AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(3, N'material',     CAST(N'Wood'             AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(3, N'padding',      CAST(N'Silk'             AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(4, N'material',     CAST(N'Gold'             AS NVARCHAR(15)))

INSERT INTO ItemAttributes

  VALUES(4, N'inscription',  CAST(N'One ring ...'     AS NVARCHAR(50)))

INSERT INTO ItemAttributes

  VALUES(4, N'size',         CAST(10                  AS INT))

INSERT INTO ItemAttributes

  VALUES(5, N'artist',       CAST(N'Claude Monet'     AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(5, N'name',         CAST(N'Field of Poppies' AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(5, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(5, N'height',       CAST(19.625              AS NUMERIC(9,3)))

INSERT INTO ItemAttributes

  VALUES(5, N'width',        CAST(25.625              AS NUMERIC(9,3)))

INSERT INTO ItemAttributes

  VALUES(6, N'artist',       CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(6, N'name',         CAST(N'The Starry Night' AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(6, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))

INSERT INTO ItemAttributes

  VALUES(6, N'height',       CAST(28.75               AS NUMERIC(9,3)))

INSERT INTO ItemAttributes

  VALUES(6, N'width',        CAST(36.25               AS NUMERIC(9,3)))

--请注意，sql_variant 数据类型被用于value 列，因为不同的属性值可能具有不同的数据类型。例如，size 属性存储整数属性值，而name 属性存储字符串属性值

 

 

--呈现ItemAttributes 表中的数据，该表具有与每个油画项目（项目5、）相对应的行以及与每个属性相对应的列。

--如果没有PIVOT 运算符，则必须编写如下所示的查询：

 

SELECT

  itemid,

  MAX(CASE WHEN attribute = 'artist'  THEN value END) AS [artist],

  MAX(CASE WHEN attribute = 'name'    THEN value END) AS [name],

  MAX(CASE WHEN attribute = 'type'    THEN value END) AS [type],

  MAX(CASE WHEN attribute = 'height'  THEN value END) AS [height],

  MAX(CASE WHEN attribute = 'width'   THEN value END) AS [width]

FROM ItemAttributes AS ATR

WHERE itemid  IN (5)--IN(5,6)

GROUP BY itemid

 

SELECT * FROM ItemAttributes WHERE itemid =5

 

--SELECT * FROM ItemAttributes WHERE itemid =6

 

--PIVOT 运算符使您可以维护更简短且更可读的代码以获得相同的结果：

 

SELECT *

FROM ItemAttributes AS ATR

  PIVOT

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

WHERE itemid IN(5,6)

 

 

SELECT * -- itemid, [artist], [name], [type], [height], [width]

FROM ItemAttributes AS ATR

  PIVOT

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

WHERE itemid IN(5,6)

 

 

SELECT

  itemid,

  MAX(CASE WHEN attribute = 'artist'  THEN value END) AS [artist],

  MAX(CASE WHEN attribute = 'name'    THEN value END) AS [name],

  MAX(CASE WHEN attribute = 'type'    THEN value END) AS [type],

  MAX(CASE WHEN attribute = 'height'  THEN value END) AS [height],

  MAX(CASE WHEN attribute = 'width'   THEN value END) AS [width]

FROM ItemAttributes AS ATR

WHERE itemid IN(5,6)

GROUP BY itemid

 

--PIVOT 作用于table_expression，它是由该查询中FROM 子句和PIVOT 子句之间的部分返回的虚拟表

SELECT *

FROM AuctionItems AS ITM

  JOIN ItemAttributes AS ATR

    ON ITM.itemid = ATR.itemid

  PIVOT

 

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

WHERE itemtype = 'Painting'

 

SELECT ITM.itemid, itemtype, whenmade, initialprice,

  [artist], [name], [type], [height], [width]

FROM AuctionItems AS ITM

  JOIN ItemAttributes AS ATR

    ON ITM.itemid = ATR.itemid

  PIVOT

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

WHERE itemtype = 'Painting'

 

 

--正如前面提到的那样，PIVOT 运算符作用于由table_expression 返回的虚拟表，而不是作用于select_list 中的列。select_list 在PIVOT 运算符执行它的操作之后计算，并且只能引用group_by_list 和column_list。这就是在select_list 中不再识别ITM 别名的原因。如果您了解这一点，您就会意识到，应当向PIVOT 提供一个只包含您希望施加作用的列的table_expression。这包括分组列（只有itemid 的一个实例，外加itemtype、whenmade 和initialprice）、枢轴列(attribute) 和值列(value)。您可以通过使用CTE 或派生表做到这一点。以下是一个使用CTE 的示例：

 

  SELECT ITM.*, ATR.attribute, ATR.value

  FROM AuctionItems AS ITM

    JOIN ItemAttributes AS ATR

      ON ITM.itemid = ATR.itemid

  WHERE ITM.itemtype = 'Painting'

;WITH PNT

AS

(

  SELECT ITM.*, ATR.attribute, ATR.value

  FROM AuctionItems AS ITM

    JOIN ItemAttributes AS ATR

      ON ITM.itemid = ATR.itemid

  WHERE ITM.itemtype = 'Painting'

)

SELECT * FROM PNT

  PIVOT

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

 

 

--以下是一个使用派生表的示例：

 

SELECT *

FROM (SELECT ITM.*, ATR.attribute, ATR.value

      FROM AuctionItems AS ITM

         JOIN ItemAttributes AS ATR

         ON ITM.itemid = ATR.itemid

      WHERE ITM.itemtype = 'Painting') AS PNT

  PIVOT

 

  (

    MAX(value)

    FOR attribute IN([artist], [name], [type], [height], [width])

  ) AS PVT

 

 

--UNPIVOT 运算符使您可以标准化预先旋转的数据。UNPIVOT 运算符的语法和元素与PIVOT 运算符类似。

SELECT itemid, attribute, value

FROM AuctionItems

  UNPIVOT

  (

    value FOR attribute IN([itemtype], [whenmade], [initialprice])

  ) AS UPV

 

SELECT itemid, attribute, value

FROM (SELECT itemid,

        CAST(itemtype     AS SQL_VARIANT) AS itemtype,

        CAST(whenmade     AS SQL_VARIANT) AS whenmade,

        CAST(initialprice AS SQL_VARIANT) AS initialprice

      FROM AuctionItems) AS ITM

  UNPIVOT

  (

    value FOR attribute IN([itemtype], [whenmade], [initialprice])

  ) AS UPV

 

--  UNPIVOT 运算符从结果中消除了value 列中的空值；因此，不能将其视为PIVOT 运算符的严格逆操作

 

SELECT itemid, attribute, value

FROM (SELECT itemid,

        CAST(itemtype     AS SQL_VARIANT) AS itemtype,

        CAST(whenmade     AS SQL_VARIANT) AS whenmade,

        CAST(initialprice AS SQL_VARIANT) AS initialprice

      FROM AuctionItems) AS ITM

  UNPIVOT

  (

    value FOR attribute IN([itemtype], [whenmade], [initialprice])

  ) AS UPV

UNION ALL

SELECT *

FROM ItemAttributes

ORDER BY itemid, attribute

 

--CROSS APPLY

 

CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT)

RETURNS TABLE

AS

RETURN

  SELECT

    CASE

      WHEN @p1 < @p2 THEN @p1

      WHEN @p2 < @p1 THEN @p2

      ELSE COALESCE(@p1, @p2)

    END AS mn,

    CASE

      WHEN @p1 > @p2 THEN @p1

      WHEN @p2 > @p1 THEN @p2

      ELSE COALESCE(@p1, @p2)

    END AS mx

GO

SELECT * FROM fn_scalar_min_max(10, 10)

 

 

CREATE TABLE T1

(

  col1 INT NULL,

  col2 INT NULL

)

 

INSERT INTO T1 VALUES(10, 20)

INSERT INTO T1 VALUES(20, 10)

INSERT INTO T1 VALUES(NULL, 30)

INSERT INTO T1 VALUES(40, NULL)

INSERT INTO T1 VALUES(50, 50)

 

--您希望为T1 中的每个行调用fn_scalar_min_max。您可以按如下方式编写CROSS APPLY 查询：

 

SELECT *

FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

 

CREATE TABLE Departments

(

  deptid    INT NOT NULL PRIMARY KEY,

  deptname  VARCHAR(25) NOT NULL,

  deptmgrid INT NULL REFERENCES Employees

)

SET NOCOUNT ON

INSERT INTO Departments VALUES(1, 'HR',           2)

INSERT INTO Departments VALUES(2, 'Marketing',    7)

INSERT INTO Departments VALUES(3, 'Finance',      8)

INSERT INTO Departments VALUES(4, 'R&D',          9)

INSERT INTO Departments VALUES(5, 'Training',     4)

INSERT INTO Departments VALUES(6, 'Gardening', NULL)

 

 

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE

(

  empid   INT NOT NULL,

  empname VARCHAR(25) NOT NULL,

  mgrid   INT NULL,

  lvl     INT NOT NULL

)

AS

BEGIN

  WITH Employees_Subtree(empid, empname, mgrid, lvl)

  AS

  (

    -- Anchor Member (AM)

    SELECT empid, empname, mgrid, 0

    FROM employees

    WHERE empid = @empid

    UNION all

    -- Recursive Member (RM)

    SELECT e.empid, e.empname, e.mgrid, es.lvl+1

    FROM employees AS e

      JOIN employees_subtree AS es

        ON e.mgrid = es.empid

  )

  INSERT INTO @TREE

    SELECT * FROM Employees_Subtree

  RETURN

END

GO

--要为每个部门的经理返回所有级别的所有下属，请使用以下查询：

 

SELECT *

FROM Departments AS D

  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

 

 

--CROSS APPLY 运算符的另一个实际运用可以满足以下常见请求：

--为每个组返回n 行。例如，以下函数返回给定客户的请求数量的最新定单：

 

USE AdventureWorks

GO

CREATE FUNCTION fn_getnorders(@custid AS INT, @n AS INT)

  RETURNS TABLE

AS

RETURN

  SELECT TOP(@n) *

  FROM Sales.SalesOrderHeader

  WHERE CustomerID = @custid

  ORDER BY OrderDate DESC

GO

--使用CROSS APPLY 运算符，可以通过下面的简单查询获得每个客户的两个最新定单：

 

SELECT O.*

FROM Sales.Customer AS C

  CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O

 

 

 

--OUTER APPLY 非常类似于CROSS APPLY，但是它还从表值函数为其返回空集的外部表中返回行。空值作为与表值函数的列相对应的列值返回。例如，修改针对上一节中的Departments 表的查询以使用OUTER APPLY 而不是CROSS APPLY，并且注意输出中的最后一行：

USE tempdb

GO

SELECT *

FROM Departments AS D

  OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

 

 

SELECT *

FROM Departments AS D

WHERE (SELECT COUNT(*)

       FROM fn_getsubtree(D.deptmgrid)) >= 3

 

 

 

USE AdventureWorks

SELECT TOP(SELECT

             COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate))

           FROM Purchasing.PurchaseOrderHeader) *

FROM Purchasing.PurchaseOrderHeader

ORDER BY OrderDate DESC