您所在的位置:首页 > 新闻中心 > 行列转换详解,透视数据
公司要闻
行列转换详解,透视数据
发布时间:2020-03-14 11:59
访问量:359
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;

在SQL Server从前的版本里,将行级数据调换为列级数据将要用到一连串CASE语句和集纳查询。即便这种方式让开采职员具有了对所重临数据开展中度调控的力量,不过编写出那么些查询是一件很麻烦的事情。

逆透视转换

必要如下,原数据如下:

图片 1

今昔要求获得那样的数据:

图片 2

应用专门的学问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);

 实行结果如下:

图片 3

借使还想进一层过滤掉含有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;
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;

图片 4

透视调换

透视数据是一种把数量从行的情形旋转为列的情事的管理。每种透视转变将关系分组、扩充及聚合多个逻辑管理阶段,每一个阶段皆有连带的要素:分组阶段管理相关的分组或行元素,扩大阶段管理有关的强大或列成分,聚合阶段管理相关的聚合成分和聚合函数。今后若是有一张表数据如下:

图片 5

作者今日急需查询出上面的结果:

图片 6

急需解析:须要在结果中为每三个雇教员和学生成一行记录,那就供给对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种因素,不包蕴别的品质):

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。

图片 7

(1)、使用守旧Select的CASE语句询问

分组集

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通过将表明式某一列中的唯一值转变为出口中的多少个列来旋转表值表明式,并在供给时对最终输出中所需的此外别的列值推行聚合。UNPIVOT与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;

UNPIVOT实例

把这张表查出来

动用如下程序填入表数据。

图片 8

1,生成别本
2,提取成分
3,删除带有NULL的行

先加一张测量检验表

一、PIVOT实例

PIVOT  (数据行)  for  要转的列  IN (转那些数据State of Qatar

  SELECT year as 年份
  , sum (case when quarter = 'Q1' then amount else 0 end) 一季度
  , sum (case when quarter = 'Q2' then amount else 0 end) 二季度
  , sum (case when quarter = 'Q3' then amount else 0 end) 三季度
  , sum (case when quarter = 'Q4' then amount else 0 end) 四季度
 FROM SalesByQuarter GROUP BY year ORDER BY year DESC
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

UNPIVOT的输入是左表表明式P,第一步,先为P中的行生成两个别本,在UNPIVOT中冒出的每一列,都会生成一个别本。因为这里的IN子句有5个列名称,所以要为各样来自行生成5个别本。结果获得的设想表准将新添一个列,用来以字符串格式保存来源列的称号(for和IN之间的,上面例子是 Employee )。第二步,依照新增加的那一列中的值平素源列中领抽出与列名对应的行。第三步,删除掉结果列值为null的行,完成那么些查询。

sql server 还协理贰个子句用于 行转列 PIVOT  是以FROM内嵌 表表达式实现的

三.UNPIVOT

我们将 custid行转变到例 

获得的结果如下:

2. 填入表数据

1. 建表

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些天)
FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
PIVOT
(
 SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
)TBL--别名一定要写

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

很醒目,UN那几个前缀注明了,它做的操作是跟PIVOT相反的,即列转行。UNPIVOT操作涉及到以下四个逻辑管理阶段。

SET NOCOUNT ON
 DECLARE @index INT
 DECLARE @q INT
 SET @index = 0
 DECLARE @year INT
 while (@index < 30)
 BEGIN
  SET @year = 2005 + (@index % 4)
  SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
  INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
  SET @index = @index + 1

出于SQL Server 二〇〇六有了新的PIVOT运算符,就不再必要CASE语句和GROUP BY语句了。(每一个PIVOT查询都关乎某种类型的聚众,因而你能够忽略GROUP BY语句。)PIVOT运算符让我们能够采纳CASE语句询问完成均等的效果与利益,可是你能够用更加少的代码就兑现,並且看起来更特出。

二、通过上边八个实例详细介绍PIVOT的经过

SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
 Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
 (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
 FROM pvt) p
UNPIVOT
 (Orders FOR Employee IN 
  (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

创造叁个发售意况表,此中,year字段表示年份,quarter字段表示季度,amount字段表示发卖额。quarter字段分别用Q1, Q2, Q3, Q4代表一、二、三、四季度。

(2)、使用PIVOT

地方UNPIVOT实例的剖释

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

图片 9

收获的结果如下: