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

透视转变

透视数据是豆蔻年华种把数据从行的气象旋转为列的气象的管理。各种透视转变将波及分组、扩展及聚合多个逻辑管理阶段,各样阶段皆有相关的因素:分组阶段管理相关的分组或行成分,扩大阶段管理有关的强大或列成分,聚合阶段管理相关的聚合成分和聚合函数。以后生龙活虎旦有一张表数据如下:

威澳门尼斯人36366com 1

本身今日亟待查询出下边包车型大巴结果:

威澳门尼斯人36366com 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卡塔尔(英语:State of Qatar) 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种成分,不带有别的属性):

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。

PIVOT通过将表明式某一列中的唯意气风发值调换为出口中的三个列来旋转表值表明式,并在供给时对最后输出中所需的此外其他列值推行聚合。UNPIVOT与PIVOT实行相反的操作,将表值表明式的列调换为列值。

   
透视是风度翩翩种通过集合和旋转把数量行转变来多少列的技艺。当透视数据时,须要规定七个元素:要在行(分组成分)中看出的因素,要在列(扩张成分)上观望的要素,要在多少部分来看的成分(聚合元素)。

先加一张测验表

逆透视转变

需求如下,原数据如下:

威澳门尼斯人36366com 3

当今亟待得到那样的数目:

威澳门尼斯人36366com 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);

 实行结果如下:

威澳门尼斯人36366com 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;

深入浅出简单的讲:PIVOT正是行转列,UNPIVOT正是列传行

SQL Server数据库中,PIVOT在救助中那样描述滴:能够行使 PIVOT
和UNPIVOT 关系运算符将表值表明式校勘为另二个表。PIVOT
通过将表明式某一列中的唯生龙活虎值转换为出口中的四个列来旋转表值表明式,并在须要时对终极输出中所需的别样此外列值实施聚合。UNPIVOT
与 PIVOT 实施相反的操作,将表值表达式的列调换为列值。

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

 

一、PIVOT实例

数据表如下所示:

把那张表查出来

1. 建表

威澳门尼斯人36366com 6

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

确立八个售货情状表,此中,year字段表示年份,quarter字段表示季度,amount字段表示发卖额。quarter字段分别用Q1,
Q2, Q3, Q4表示风华正茂、二、三、四季度。

with C as
(
select YEAR(orderdate) as orderyear,MONTH(orderdate) AS ordermonth,val
from Sales.OrderValues
)
select *
from C
  PIVOT(sum(val) 
    for ordermonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as P

威澳门尼斯人36366com 7

 CREATE TABLE SalesByQuarter ( year INT, -- 年份 quarter CHAR, -- 季度 amount MONEY -- 总额 )

威澳门尼斯人36366com 8

大家将 custid行转变来例 

2. 填入表数据

触类旁通七下,为每一个主顾重返前段时间的5此订单的订单消息

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注