>

逆透视及分组集,透视数据

- 编辑:金沙国际平台登录 -

逆透视及分组集,透视数据

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

透视调换

透视数据是生龙活虎种把数据从行的气象旋转为列的景观的拍卖。每一种透视转变将关联分组、扩张及聚合四个逻辑管理阶段,各类阶段都有有关的因素:分组阶段管理有关的分组或行成分,扩充阶段管理相关的扩张或列成分,聚合阶段管理有关的聚合成分和聚合函数。以后假如有一张表数据如下:

图片 1

自家几眼下急需查询出下边包车型地铁结果:

图片 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举行透视转变呢?请看下边:

先剖判,假若是动态查询,那么早晚须要拼凑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 二零零七引进了一个T-SQL特有的表运算符PIVOT,PIVOT运算符一样涉嫌多少个逻辑管理阶段(分组、扩充和聚集卡塔 尔(阿拉伯语:قطر‎。注意,平时不直接把PIVOT运算符应用到源表,而是将其选用到多个表表明式(该表表明式只含有透视转变须求的3种因素,不含有别的品质卡塔 尔(英语:State of Qatar):

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。

先加一张测量检验表

逆透视转变

急需如下,原数据如下:

图片 3

现行反革命要求获得如此的数量:

图片 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);

 实行结果如下:

图片 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;

图片 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;

图片 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  (数据行卡塔 尔(英语:State of Qatar)  for  要转的列  IN (转那么些数据)

 

本文由 数据库发布,转载请注明来源:逆透视及分组集,透视数据