SQL经典:T-SQL中的透视和逆透视解析

数据库 SQL Server
SQL语句的透视和逆透视功能相信大家并不陌生。本文就以实例的形式演示了SQL查询中的透视和逆透视过程,供读者参考。

SQL查询时,我们可能会用到T-SQL透视和逆透视的功能,比如我们对销售表中的列进行查询时就用到了。透视运算符要使用子查询中的数据进行聚合运算,然后再输出。本文通过实例详细讲述了这一过程,下面先说透视。

透视

简单的说就是行列转换。假设一个销售表中存放着产品号,产品折扣,产品价格三个列,每一种产品号可能有多种折扣,每一种折扣只对应一个产品价格。下面贴出建表语句和插入数据语句。

  1. create table SalesOrderDetail(  
  2.  
  3. ProductID int /*unique多谢wuu00的提醒*/,  
  4.  
  5. 3 UnitPriceDiscount float,  
  6.  
  7. 4 ProductPrice float  
  8.  
  9. )  
  10.  
  11. insert into SalesOrderDetail values  
  12.  
  13. (711,.00,12),  
  14.  
  15. (711,.00,13),  
  16.  
  17. (711,.02,17),  
  18.  
  19. (711,.02,16),  
  20.  
  21. (711,.05,19),  
  22.  
  23. (711,.05,20),  
  24.  
  25. (711,.10,21),  
  26.  
  27. (711,.10,22),  
  28.  
  29. (711,.15,23),  
  30.  
  31. (711,.15,24),  
  32.  
  33. (747,.00,41),  
  34.  
  35. (747,.00,42),  
  36.  
  37. (747,.02,45),  
  38.  
  39. (747,.02,46),  
  40.  
  41. (776,.20,50),  
  42.  
  43. (776,.20,49),  
  44.  
  45. (776,.35,52),  
  46.  
  47. (776,.35,53) 

首先来看一条查询语句

  1. select ProductID,UnitPriceDiscount,SUM(ProductPrice) as SumPrice   
  2.  
  3. from SalesOrderDetail   
  4.  
  5. group by ProductID,UnitPriceDiscount  
  6.  
  7. order by ProductID,UnitPriceDiscount 

这条语句查询每一种产品针对每一种折扣的价钱总和,查询结果如下图1

SQL点滴:T-SQL中的透视和逆透视解析

图1

从图中我们可以看出771号产品有4种折扣,747号产品有2种折扣,776号产品有2种折扣。现在如果我们想知道每一种产品折扣,每一种产品的销售总价是多少,如下图2

SQL点滴:T-SQL中的透视和逆透视解析

图2

如图对于折扣0,产品711的总价是25,对以折扣0.02,产品711的总价是33等等不再列举。原来的行是产品号,现在产品号变成了列,原来的折扣变成了现在的第一列。这就是数据透视的效果。下面我们开看看是这个效果是如何用语句实现的。

  1. select * from   
  2.  
  3. (select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so  
  4.  
  5. pivot  
  6.  
  7. (  
  8.  
  9. sum(so.ProductPrice) for so.ProductID in([711],[747],[776])  
  10.  
  11. 6 ) as pt  
  12.  
  13. order by UnitPriceDiscount 

首选创建子查询(select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so ,透视运算符要使用这个子查询中的数据进行聚合运算,此外输出显示也要用到子查询中的列。代码生成一个别名为so的表值表达式。在这个表中使用pivot在特定的列上进行聚合,这里是对so.ProductPrice进行聚合,聚合针对so.ProductID进行。

在这个例子中对三种产品的中的每一种创建一个列。这个相当于group by,从so表达式中进行数据筛选。不过这里没有选出ProductPrice,仅仅生成每行三个列,每一种产品为一个列的结果集。因此带有povit的表值表达式生成一个临时的结果集,将这个结果集命名为pt,使用这个结果集生成我们需要的输出。如果想要得到一个更加合适的列名可以修改筛选条件。如下:

  1. select pt.UnitPriceDiscount,[711] as Product711,[747] as Product747,[776] as Product747 from  
  2.  
  3. (select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so  
  4.  
  5. pivot  
  6.  
  7. (  
  8.  
  9. sum(so.ProductPrice) for so.ProductID in([711],[747],[776])  
  10.  
  11. ) as pt  
  12.  
  13. order by UnitPriceDiscount 

输出的结果如下图3

SQL点滴:T-SQL中的透视和逆透视解析

图3

#p#

逆透视

这次我们首先看语句和查询结果再分析,语句如下:

  1. select ProductID,UnitPriceDiscount,ProductPrice  
  2.  
  3. from  
  4.  
  5. (select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1  
  6.  
  7. unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up2  
  8.  
  9. 5 order by ProductID 

查询结果如下图4:

SQL点滴:T-SQL中的透视和逆透视解析

图4

首先我们来看看逆透视得到了一个什么样的结果。对于每一种产品的每一种折扣查询得到他们的合计售价,这个和上面图1中的结果是一样的,是的,它和透视之前的结果是相同的。逆透视和透视并不是完全相反。Pivot会执行聚合,把可能存在的多个行合并输出得到一行。由于已经进行了合并,unpivot无法重新生成原始的表值表达式,unpivot输入中的null值将在输出中消失,尽管在pivot操作之前输入中可能存在原始的null值。如图5是他们的比较。在图中我们可以看到NULL值下面一个图中没有NULL值,刚好有9行。下图把他们放在一起比较。

SQL点滴:T-SQL中的透视和逆透视解析

图5

下面我们来剖析一下上面的语句到底做了些什么。首先是一个表值函数(select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1,这个表值函数从透视结果,也就是临时表中,然后针对每一个产品号进行逆透视:unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up2,然后从逆透视结果中选择ProductID ,ProductPrice,从表值函数中选择UnitPriceDiscount。

#p#

延伸阅读

一个例子还不足以让我们理解这个语句,下面来看看TechNet中的例子。

  1. SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product  
  2.  
  3. GROUP BY DaysToManufacture; 

这个语句查出Product表中的制造时间和平均成本,得到如下的结果

SQL点滴:T-SQL中的透视和逆透视解析

图6

如图可以看到没有制造时间为3天的产品,这里留下一个伏笔,在透视之后会出现一个NULL值。下面使用透视语句对它进行行列转换,就是使用0,1,2,3来作为列,使用具体的制造成本作为行数据。语句如下

  1. select   
  2.  
  3. 'AverageCost' as Cost_Sorted_By_Production_Days,  
  4.  
  5. [0],[1],[3],[4]  
  6.  
  7. from  
  8.  
  9. (select DaysToManufacture,StandardCost from Production.Product) as SourceTable  
  10.  
  11. 6 pivot  
  12.  
  13. (avg(StandardCost) for DaysToManufacture in ([0],[1],[3],[4])) as PivotTable 

依旧,首先用一个表值表达式把要透视的列和透视的项选择出来,然后使用透视语句针对每一个项计算平均成本,最后从这个透视结果中选择出结果。

结果如下图7,我们可以看到制造时间为3天的产品没有一个对应的平均成本。

SQL点滴:T-SQL中的透视和逆透视解析

图7

下面这个例子稍微复杂一点。

  1. SELECT VendorID,count(PurchaseOrderID) as PurchaseCunt  
  2.  
  3. FROM Purchasing.PurchaseOrderHeader group by VendorID 

这条语句查询得到每个供应商和他对应的交易号的个数,也就是每个供应商成交的交易次数。如图8列举出部分结果

SQL点滴:T-SQL中的透视和逆透视解析

图8

从图中我们可以看到供应商1共成交51比交易,供应商2共成交51笔交易。如果我们想查出这些交易分别是和那些雇员成交的应该怎么写呢?首先我们来看看表中全部的雇员情况。

  1. select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader 

查询结果如图9

SQL点滴:T-SQL中的透视和逆透视解析

图9

如上图我们可以看到共有12个雇员有成交记录。对于这些雇员,如下查询语句

  1. SELECT   
  2.  
  3. VendorID,  
  4.  
  5. [164] AS Emp164,  
  6.  
  7. [198] AS Emp198,  
  8.  
  9. [223] AS Emp223,  
  10.  
  11. [231] AS Emp231,  
  12.  
  13. [233] AS Emp233,  
  14.  
  15. [238] as Emp238,  
  16.  
  17. [241] as Emp241,  
  18.  
  19. [244] as Emp244,  
  20.  
  21. [261] as Emp261,  
  22.  
  23. [264] as Emp264,  
  24.  
  25. [266] as Emp266,  
  26.  
  27. [274] as Emp274  
  28.  
  29. 15 FROM   
  30.  
  31. (SELECT PurchaseOrderID,EmployeeID,VendorID  
  32.  
  33. FROM Purchasing.PurchaseOrderHeader) p  
  34.  
  35. PIVOT  
  36.  
  37. (  
  38.  
  39. COUNT (PurchaseOrderID)  
  40.  
  41. FOR EmployeeID IN  
  42.  
  43. ( [164], [198], [223], [231],[233],[238],[241],[244],[261],[264],[266],[274])  
  44.  
  45. ) AS pvt  
  46.  
  47. 24 ORDER BY pvt.VendorID; 

查询结果如下图10

SQL点滴:T-SQL中的透视和逆透视解析

图10

可以 简单地计算一下1+4+3+5+4+4+4+5+5+4+5+6+2刚好等于51,分开来看就是1号供应商分别和164号雇员成交4比记录,和198号雇员成交3比记录等等。

关于透视和逆透视的知识就介绍到这里,谢谢大家!

【编辑推荐】

  1. SQL Server如何动态生成分区脚本
  2. 用FOR XML PATH将查询结果以XML输出
  3. 浅述SQL Server的Replication技术创建技巧
  4. 简述SQL Server Replication的常见错误及其处理
  5. 如何在SQL Server 2005中使用作业实现备份和特定删除
责任编辑:赵鹏 来源: 博客园
相关推荐

2010-07-20 13:52:27

SQL Server

2021-06-08 09:18:54

SQLPandas数据透视表

2023-08-15 08:26:34

SQL Server查找死锁

2010-06-30 14:54:42

SQL Server

2010-07-06 10:36:35

SQL Server

2010-10-19 16:06:26

SQL Server索

2011-03-31 09:30:27

SQL Server数管理SQL

2011-10-19 10:07:16

T-SQL查询变量

2010-07-19 13:22:45

SQL Server

2009-05-06 17:31:17

SQL EnlightT-SQL分析器

2010-12-06 09:26:23

SQL Server

2013-01-05 13:49:00

2011-02-25 14:42:10

SQLwith关键字

2010-09-15 08:53:50

SQL Server

2010-07-13 10:35:20

SQL Server2

2023-09-20 00:33:23

SQL数据库

2011-08-24 16:36:00

T-SQL

2011-04-01 16:30:26

T-SQLDateTime

2014-04-28 14:06:41

2011-04-01 15:53:20

T-SQLDateTime
点赞
收藏

51CTO技术栈公众号