山东通信局报备网站,佛山网上房地产官网,万邦工程管理咨询有限公司,免费网站空间申请教程原文:SQL点滴19—T-SQL中的透视和逆透视透视 今天抽一点时间来看看透视和逆透视语句#xff0c;简单的说就是行列转换。假设一个销售表中存放着产品号#xff0c;产品折扣#xff0c;产品价格三个列#xff0c;每一种产品号可能有多种折扣#xff0c;每一种折扣只对应一个…原文:SQL点滴19—T-SQL中的透视和逆透视 透视 今天抽一点时间来看看透视和逆透视语句简单的说就是行列转换。假设一个销售表中存放着产品号产品折扣产品价格三个列每一种产品号可能有多种折扣每一种折扣只对应一个产品价格。下面贴出建表语句和插入数据语句。 1 create table SalesOrderDetail( 2 ProductID int /*unique多谢wuu00的提醒*/, 3 UnitPriceDiscount float, 4 ProductPrice float 5 ) 6 insert into SalesOrderDetail values 7 (711,.00,12), 8 (711,.00,13), 9 (711,.02,17),10 (711,.02,16),11 (711,.05,19),12 (711,.05,20),13 (711,.10,21),14 (711,.10,22),15 (711,.15,23),16 (711,.15,24),17 (747,.00,41),18 (747,.00,42),19 (747,.02,45),20 (747,.02,46),21 (776,.20,50),22 (776,.20,49),23 (776,.35,52),24 (776,.35,53) 首先来看一条查询语句 1 select ProductID,UnitPriceDiscount,SUM(ProductPrice) as SumPrice 2 from SalesOrderDetail 3 group by ProductID,UnitPriceDiscount4 order by ProductID,UnitPriceDiscount 这条语句查询每一种产品针对每一种折扣的价钱总和查询结果如下图1 图1 从图中我们可以看出771号产品有4种折扣747号产品有2种折扣776号产品有2种折扣。现在如果我们想知道每一种产品折扣每一种产品的销售总价是多少如下图2 图2 如图对于折扣0产品711的总价是25对以折扣0.02产品711的总价是33等等不再列举。原来的行是产品号现在产品号变成了列原来的折扣变成了现在的第一列。这就是数据透视的效果。下面我们开看看是这个效果是如何用语句实现的。 1 select * from 2 (select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so3 pivot4 (5 sum(so.ProductPrice) for so.ProductID in([711],[747],[776])6 ) as pt7 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 from2 (select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so3 pivot4 (5 sum(so.ProductPrice) for so.ProductID in([711],[747],[776])6 ) as pt7 order by UnitPriceDiscount 输出的结果如下图3 图3 逆透视 这次我们首先看语句和查询结果再分析语句如下 1 select ProductID,UnitPriceDiscount,ProductPrice2 from3 (select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up14 unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up25 order by ProductID 查询结果如下图4 图4 首先我们来看看逆透视得到了一个什么样的结果。对于每一种产品的每一种折扣查询得到他们的合计售价这个和上面图1中的结果是一样的是的它和透视之前的结果是相同的。逆透视和透视并不是完全相反。Pivot会执行聚合把可能存在的多个行合并输出得到一行。由于已经进行了合并unpivot无法重新生成原始的表值表达式unpivot输入中的null值将在输出中消失尽管在pivot操作之前输入中可能存在原始的null值。如图5是他们的比较。在图中我们可以看到NULL值下面一个图中没有NULL值刚好有9行。下图把他们放在一起比较。 图5 下面我们来剖析一下上面的语句到底做了些什么。首先是一个表值函数(select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1这个表值函数从透视结果也就是临时表中然后针对每一个产品号进行逆透视unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up2然后从逆透视结果中选择ProductID ,ProductPrice从表值函数中选择UnitPriceDiscount。 延伸阅读 一个例子还不足以让我们理解这个语句下面来看看TechNet中的例子。 SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture; 这个语句查出Product表中的制造时间和平均成本得到如下的结果 图6 如图可以看到没有制造时间为3天的产品这里留下一个伏笔在透视之后会出现一个NULL值。下面使用透视语句对它进行行列转换就是使用01,2,3来作为列使用具体的制造成本作为行数据。语句如下 1 select 2 AverageCost as Cost_Sorted_By_Production_Days,3 [0],[1],[3],[4]4 from5 (select DaysToManufacture,StandardCost from Production.Product) as SourceTable6 pivot7 (avg(StandardCost) for DaysToManufacture in ([0],[1],[3],[4])) as PivotTable 依旧首先用一个表值表达式把要透视的列和透视的项选择出来然后使用透视语句针对每一个项计算平均成本最后从这个透视结果中选择出结果。结果如下图7我们可以看到制造时间为3天的产品没有一个对应的平均成本。 图7 下面这个例子稍微复杂一点。 1 SELECT VendorID,count(PurchaseOrderID) as PurchaseCunt2 FROM Purchasing.PurchaseOrderHeader group by VendorID 这条语句查询得到每个供应商和他对应的交易号的个数也就是每个供应商成交的交易次数。如图8列举出部分结果 图8 从图中我们可以看到供应商1共成交51比交易供应商2共成交51笔交易。如果我们想查出这些交易分别是和那些雇员成交的应该怎么写呢首先我们来看看表中全部的雇员情况。 select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader 查询结果如图9 图9 如上图我们可以看到共有12个雇员有成交记录。对于这些雇员如下查询语句 1 SELECT 2 VendorID, 3 [164] AS Emp164, 4 [198] AS Emp198, 5 [223] AS Emp223, 6 [231] AS Emp231, 7 [233] AS Emp233, 8 [238] as Emp238, 9 [241] as Emp241,10 [244] as Emp244,11 [261] as Emp261,12 [264] as Emp264,13 [266] as Emp266,14 [274] as Emp27415 FROM 16 (SELECT PurchaseOrderID,EmployeeID,VendorID17 FROM Purchasing.PurchaseOrderHeader) p18 PIVOT19 (20 COUNT (PurchaseOrderID)21 FOR EmployeeID IN22 ( [164], [198], [223], [231],[233],[238],[241],[244],[261],[264],[266],[274])23 ) AS pvt24 ORDER BY pvt.VendorID; 查询结果如下图10 图10 可以 简单地计算一下1435444554562刚好等于51分开来看就是1号供应商分别和164号雇员成交4比记录和198号雇员成交3比记录等等。
