《SQLServer2005PIVOT运算符的使用一列多行数据合并为一行》由会员分享，可在线阅读，更多相关《SQLServer2005PIVOT运算符的使用一列多行数据合并为一行（8页珍藏版）》请在金锄头文库上搜索。
1、SQLServer2005PIVOT运算符的使用PIVOT,UNPIVOT运算符是SQLServer2005支持的新功能之一，主要用来实现行到列的转换。本文主要介绍PIVOT运算符的操作，以及如何实现动态PIVOT的行列转换。关于UNPIVOT及SQLServer2000下的行列转换请参照本人的其它文章。一、使用PIVOT和UNPIVOT命令的SQLServer版本要求1、数据库的最低版本要求为SQLServer2005或更高。2、必须将数据库的兼容级别设置为90或更高。3、查看我的数据库版本及兼容级别。如果不知道怎么看数据库版本或兼容级别的话可以在SQLServerManagementStu
2、dio新建一个查询窗口输入：printversion,运行之后在我的本机上得到：MicrosoftSQLServer2008R2(RTM)-10.50.1617.0(X64)Apr22201119:23:43Copyright(c)MicrosoftCorporationDataCenterEdition(64-bit)onWindowsNT6.1(Build7601:ServicePack1)我们选择一个数据库，然后【右键】-【属性】，选择【选项】得到下图的信息。1.加4H三-一1口人gJ3芋文件文中目岁送生上1那娜晾/哪扩展国It小激停3平期日志特医1排像荒t辛版则CJ：ChLnesJE.
3、CIJISJJJ占祝别口：3LSAtvAh-sncauqqj他诰颂10):5PLServer5pLServer3000(BO)BME师SULServer2009(100)B5H寸Br4lseTBroker已启用FoIsg：ServiceBroker制卜吧曲41龙】Tdd-be&-MH355bc751日册吊瓦。5优先端FQn色交页骏注TOMfJAjGejCTECHOT时标毁砧尊标做BKL=B耗交时关1渊母为盂已启用Fd.st条喷MULL默认值Fa1e6邺IHULLS已启用1r415EKMSI售吉已启用瞥久.由场无已启mF1etVsu-DtEinal存描格式已J0用琴曲化倒单连巷军联的jtuU结果
4、为N&1TilstEk噬归年皎盍已启用Fa15可百有方将由仁M值Ils跨数拈阵所有如窗接已后用FUmE即阻关住优我己启用Foist效值含其中itFiLseHNrbil口百用FUrA1SIWVLL过认值幅匚物百1在确认数据库的版本和兼容级别符合1、2点的要求后，才可以接着继续往下学习。、PIVOT的语法SELECTnon-pivotedcolumn,-optionaladditionalnon-pivotedcolumns,-optionalfirstpivotedcolumn,additionalpivotedcolumnsFROM(SELECTqueryproducingsqldatafor
5、pivot-selectpivotcolumnsasdimensionsand-valuecolumnsasmeasuresfromsqltables)ASTableAliasPIVOT(columnforaggregationormeasurecolumn)-MIN,MAX,SUM,etcFORIN(firstpivotedcolumn,，lastpivotedcolumn)ASPivotTableAliasORDERBYclause-optional三、PIVOT的使用例子1、静态PIVOT的用法为演示，从NorthWind数据库中提取一些记录生成新的Orders表，然后使用PIVOT将行
6、转换到列。USEtempdbGOSELECTYEAR(OrderDate)ASYear,CustomerID,od.QuantityINTOdbo.OrdersFROMNorthWind.OrdersASoJOINNorthWind.OrderDetailsASodONo.OrderID=od.OrderIDWHEREo.CustomerIDIN(BONAP,BOTTM,ANTON)SELECTCustomerID,1996,1997,1998FROMdbo.OrdersPIVOT(SUM(Quantity)FORYearIN(1996,1997,1998)ASx/*TSQL中pivot的结构
7、： 用于生成pivot数据源的源表，作为一个输入表 pivot表 聚合列及透视列的选择TSQL中pivot的实现：1-上例中Orders表相当于是一个输入表。包含了CustomerID,Year,Quantity三个列。Year是透视列，用于生成维度。pivot首先将聚合列之外的列进行分组，并对其实现聚合。本列中则是对聚合列Quantity之外的列先实现分组，即对CustomerID,Year进行分组，并对其Quantity实现聚合，相当于先做如下处理：*/SELECTCustomerID,Year,SUM(Quantity)ASTotalFROMdbo.OrdersGROUPBYCustom
8、erID,YearORDERBYCustomerID/*Result:CustomerIDYearTotalANTON199624ANTON1997295ANTON199840BONAP1996181BONAP1997486BONAP1998313BOTTM199681BOTTM1997454BOTTM1998421*/*2-pivot根据FORYearIN子句中的值，在结果集中来建立对应的新列，本例中即是列1996,1997,1998对于新列1996,1997,1998中的取值，取中间结果集中与之相对应的值。如对于客户ANTON,列中的值就选择中间结果中对应的Total值，同理列中为。并将中
9、间结果pivot表命名为x。3-最外层的SELECT语句从pivot表生成最终结果，此处因Orders表仅有列，故直接将结果用一个SELECT返回，有嵌套的SELECT参照下例。-结果：CustomerID199619971998ANTON2429540BONAP181486313BOTTM81454421*/以下是为输入表多于一列的例子，数据来源于SQLServer2005的AdventureWorks,其实现的原理同上。SELECT*FROM(SELECTYEAR(DueDate)Year,CASEMONTH(DueDate)WHEN1THENJanuaryWHEN2THENFebruar
10、yWHEN3THENMarchWHEN4THENAprilWHEN5THENMayWHEN6THENJuneWHEN7THENJulyWHEN8THENAugustWHEN9THENSeptemberWHEN10THENOctoberWHEN11THENNovemberWHEN12THENDecemberENDASMonth,ProductID,OrderQtyFROMProduction.WorkOrder)ASWorkOrderPIVOT(SUM(OrderQty)FORMonthIN(January/February,March,April,May,June,July,August,Se
11、ptember,October,November,December)ASxORDERBYYear,ProductID-Result:末尾部分省略/*YearProductIDJanuaryFebruaryMarchAprilMayJuneJulyAugust200238480168701296095301939014170262003587020023161842370429102252473834967624107782002324184237042910225247383496754610600200232792118521455112623691748377353002002328414
12、1048872458127299217862632*/2、动态PIVOT的使用USEAdventureWorks;GO-卅种生成透视列的方法，使用了COALESCE来联接字符串DECLAREPivotColHeaderVARCHAR(MAX)SELECTPivotColHeader=COALESCE(PivotColHeader+,+cast(Nameasvarchar)+，+cast(Nameasvarchar)+)-示例中Name转换为varchar或char注意：在CAST和CONVERT中使用varchar时，显示n的默认值为FROMSales.SalesTerritoryGROUPBYName/*-第二种生成透视列的方法，使用了FORXMLPATH方法SELECTPivotColHeader=STUFF(SELECTDISTINCT,+cast(Nameasvarchar)+FROMSales.SalesTerritoryFORXMLPATH(
