88bf必发娱乐 7

笔记-Microsoft SQL Server 2008技术内幕:T-SQL语言基础-07 透视、逆透视及分组集

透视数据实际上就是行状态转为例状态

透视转换

透视数据是一种把数据从行的状态旋转为列的状态的处理。每个透视转换将涉及分组、扩展及聚合三个逻辑处理阶段,每个阶段都有相关的元素:分组阶段处理相关的分组或行元素,扩展阶段处理相关的扩展或列元素,聚合阶段处理相关的聚合元素和聚合函数。现在假设有一张表数据如下:

88bf必发娱乐 1

我现在需要查询出下面的结果:

88bf必发娱乐 2

需求分析:需要在结果中为每一个雇员生成一行记录,这就需要对Orders表中的行按照其empid列进行分组;从结果看,还需要为每一个客户生成一个不同的结果列,那么扩展元素就是custid列;最后还需要对数据进行聚合(本例中为SUM)。以下代码是使用标准SQL进行透视转换:

SELECT empid,
  SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
  SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
  SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
  SUM(CASE WHEN custid = 'D' THEN qty END) AS D  
FROM dbo.Orders
GROUP BY empid;

※※※※※ 补充,如果要转为动态的查询,即不固定对A、B、C、D进行透视转换呢?请看下面:

88bf必发娱乐,先分析,如果是动态查询,那么肯定需要拼凑SQL语句,即对“SUM(CASE WHEN
custid = ‘A’ THEN qty END) AS A,”这一部分进行拼凑。首先想到要用“SELECT
custid FROM [tempdb].[dbo].[Orders] GROUP BY
 custid”把A、B、C、D等数据GROUP BY
查出来,然后对这个数据集使用游标循环拼凑出SQL语句,但是现在还有更方便的方法。先看一个测试:

DECLARE @temp NVARCHAR(50);
SET @temp = '';
SELECT  @temp = @temp + ',' + custid
FROM    ( SELECT    custid
          FROM      [tempdb].[dbo].[Orders]
          GROUP BY  custid
        ) AS T;
PRINT @temp;

上面这段SQL会输出“,A,B,C,D”,这说明了想循环读取数据集并赋值不一定要用游标,别忘了SELECT也是可以赋值的!所以透视转换的动态SQL如下:

DECLARE @sql NVARCHAR(800);
SET @sql = 'SELECT empid';
SELECT  @sql = @sql + ',SUM(CASE WHEN custid=''' + custid
        + ''' THEN qty END) AS ' + QUOTENAME(custid)
FROM    ( SELECT    custid
          FROM      [tempdb].[dbo].[Orders]
          GROUP BY  custid
        ) AS T

SET @sql = @sql + ' FROM dbo.Orders GROUP BY empid';

EXEC(@sql);

 

下面是使用T-SQL
PIVOT运算符进行透视转换。SQL Server
2005引入了一个T-SQL特有的表运算符PIVOT,PIVOT运算符同样涉及三个逻辑处理阶段(分组、扩展和聚合)。注意,一般不直接把PIVOT运算符应用到源表,而是将其应用到一个表表达式(该表表达式只包含透视转换需要的3种元素,不包含其他属性):

SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

上面代码中PIVOT操作符并没有直接操作Orders表,而是对一个名为D的派生表进行操作,该派生表只包含透视转换元素empid、custid、qty。

先加一张测试表

逆透视转换

需求如下,原数据如下:

88bf必发娱乐 3

现在需要得到这样的数据:

88bf必发娱乐 4

使用标准SQL进行逆透视转换。逆透视转换的标准SQL解决方案非常明确地要实现3个逻辑处理阶段:生成副本、提取元素和删除不相关的交叉。

SELECT empid, custid,
  CASE custid
    WHEN 'A' THEN A
    WHEN 'B' THEN B
    WHEN 'C' THEN C
    WHEN 'D' THEN D    
  END AS qty
FROM dbo.EmpCustOrders
  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

 执行结果如下:

88bf必发娱乐 5

如果还想进一步过滤掉含有null值的数据,则可以这样:

SELECT *
FROM (SELECT empid, custid,
        CASE custid
          WHEN 'A' THEN A
          WHEN 'B' THEN B
          WHEN 'C' THEN C
          WHEN 'D' THEN D    
        END AS qty
      FROM dbo.EmpCustOrders
        CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
WHERE qty IS NOT NULL;

 使用T-SQL的UNPIVOT运算符进行逆透视转换:

SELECT empid, custid, qty
FROM dbo.EmpCustOrders
  UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);

SELECT * FROM dbo.Orders;

分组集

GROUPING SETS从属子句:

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY
  GROUPING SETS
  (
    (empid, custid),
    (empid),
    (custid),
    ()
  );

CUBE从属子句

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);

 

把这张表查出来

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

88bf必发娱乐 6

我们将 custid行转换成例 

SELECT empid,
SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
SUM(CASE WHEN custid = 'D' THEN qty END) AS D 
FROM dbo.Orders
GROUP BY empid;

88bf必发娱乐 7

sql server 还支持一个子句用于 行转列 PIVOT  是以FROM内嵌 表表达式实现的

SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

PIVOT  (数据行)  for  要转的列  IN (转那几个数据)