小说阅读网,官场小说排行榜,玄幻小说完本

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案

SQLServer2005新功能之PIVOT的描述

以下的文章主要描述的是SQL Server 2005新功能之PIVOT，在工具的升級(jí)中，我個(gè)人認(rèn)為首先我們的看看這個(gè)工具，其主要是在哪些功能上得到加強(qiáng)，所以今天我們就來(lái)看看SQL2005這個(gè)PIVOT吧。PIVOT 關(guān)系運(yùn)算符對(duì)表值表達(dá)式進(jìn)行操作以獲得另一個(gè)表。

創(chuàng)新互聯(lián)建站專(zhuān)業(yè)IDC數(shù)據(jù)服務(wù)器托管提供商，專(zhuān)業(yè)提供成都服務(wù)器托管，服務(wù)器租用，綿陽(yáng)主機(jī)托管，綿陽(yáng)主機(jī)托管，成都多線服務(wù)器托管等服務(wù)器托管服務(wù)。

PIVOT 通過(guò)將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來(lái)轉(zhuǎn)換表。工具的升級(jí)，我以為得先看看這個(gè)工具在哪些功能上得到加強(qiáng)，今天我們就看看SQL2005這個(gè)PIVOT吧。PIVOT 關(guān)系運(yùn)算符對(duì)表值表達(dá)式進(jìn)行操作以獲得另一個(gè)表。PIVOT 通過(guò)將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來(lái)轉(zhuǎn)換表值表達(dá)式，并在必要時(shí)對(duì)最終輸出中所需的任何其余的列值執(zhí)行聚合。

記得我們?cè)赟QL2000中要用聚合和CASE語(yǔ)句完成一個(gè)行列轉(zhuǎn)換吧，特別當(dāng)待轉(zhuǎn)成列的數(shù)據(jù)不定時(shí)，我們往往構(gòu)造動(dòng)態(tài)SQL，然后用EXEC來(lái)運(yùn)行。

環(huán)境準(zhǔn)備：

 
 
 
  
  
  -- Author: happyflsytone  
  
  
  -- Version:V1.001  
  
  
  -- Date:2008-09-18 10:20:53  
  
  
  -- Test Data: ta  
  
  
  IF OBJECT_ID('ta') IS NOT NULL  
  
  
  DROP TABLE ta  
  
  
  ;  
  
  
  CREATE TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)  
  
  
  ;  
  
  
  INSERT INTO ta  
  
  
  SELECT 1,'HN','CS','abc',1 UNION ALL  
  
  
  SELECT 2,'HN','CS','abcd',2 UNION ALL  
  
  
  SELECT 3,'HN','CD','abcd' ,3UNION ALL  
  
  
  SELECT 4,'HN','HY','ae' ,4  
  
  
  ;

我們先來(lái)回顧SQL2000的行列轉(zhuǎn)換,比如我們對(duì)上例程把col3轉(zhuǎn)列顯示，并把col4的和當(dāng)對(duì)應(yīng)列值。我們分兩種情況來(lái)討論：

一、當(dāng)col3的列值固定就是'abc','abcd','ae'三種情況

 
 
 
  
  
  SELECT  
  
  
  col1,  
  
  
  col2,  
  
  
  [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),  
  
  
  [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),  
  
  
  [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)  
  
  
  FROM ta  
  
  
  GROUP BY col1,col2  
  
  
  /*  
  
  
  col1 col2 abc abcd ae  
  
  
  HN CD 0 3 0  
  
  
  HN CS 1 2 0  
  
  
  HN HY 0 0 4

(3 行受影響)

二、當(dāng)col3的列值不固定時(shí)就運(yùn)用動(dòng)態(tài)SQL，其實(shí)也就是構(gòu)造一個(gè)sum(CASE WHEN ...)SQL字符串

 
 
 
  
  
  DECLARE @s varchar(8000)  
  
  
  SELECT @s = isnull(@s+',  
  
  
  ','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'  
  
  
  FROM ( SELECT distinct col3 FROM ta) a  
  
  
  SET @s = 'SELECT  
  
  
  col1,  
  
  
  col2,  
  
  
  '+@s + '  
  
  
  FROM ta  
  
  
  GROUP BY  
  
  
  col1,col2'  
  
  
  EXEC(@s)  
  
  
  /*  
  
  
  col1 col2 abc abcd ae  
  
  
  HN CD 0 3 0  
  
  
  HN CS 1 2 0  
  
  
  HN HY 0 0 4

(3 行受影響)

我們先輸入這個(gè)@S看看是什么東東，只要加上print @s

 
 
 
  
  
  SELECT  
  
  
  col1,  
  
  
  col2,  
  
  
  [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),  
  
  
  [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),  
  
  
  [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)  
  
  
  FROM ta  
  
  
  GROUP BY  
  
  
  col1,col2

其實(shí)就是上面我們構(gòu)造的固定列值的SQL嘛。

好，現(xiàn)在們開(kāi)始在2005中實(shí)現(xiàn)這個(gè)功能，先來(lái)看看2005的FROM子句的定義(關(guān)于如何看這個(gè)定義請(qǐng)參照SQL2005的文檔約定及Transate-SQL語(yǔ)法約定)：

 
 
 
  
  
  [ FROM {  } [ ,...n ] ]  
  
  
   ::=  
  
  
  {  
  
  
    
  
  
  }  
  
  
   ::=  
  
  
  table_source PIVOT  table_alias  
  
  
   ::=  
  
  
  ( aggregate_function ( value_column )  
  
  
  FOR pivot_column  
  
  
  IN (  )  
  
  
  )  
  
  
   ::=  
  
  
  column_name [ , ... ]

pivot_column 和 value_column 是 PIVOT 運(yùn)算符使用的組合列。PIVOT 遵循以下過(guò)程獲得輸出結(jié)果集：

對(duì)分組列的 input_table 執(zhí)行 GROUP BY，為每個(gè)組生成一個(gè)輸出行。

輸出行中的分組列獲得 input_table 中該組的對(duì)應(yīng)列值。

通過(guò)執(zhí)行以下操作，為每個(gè)輸出行生成列列表中的列的值：

針對(duì) pivot_column，對(duì)上一步在 GROUP BY 中生成的行另外進(jìn)行分組。

對(duì)于 column_list 中的每個(gè)輸出列，選擇滿足以下條件的子組：

 
 
 
  
  
  pivot_column = CONVERT(, 'output_column')

針對(duì)此子組上的 aggregate_function 對(duì) value_column 求值，其結(jié)果作為相應(yīng)的 output_column 的值返回。如果該子組為空，SQL Server 2005 將為該 output_column 生成空值。如果聚合函數(shù)是 COUNT，且子組為空，則返回零 (0)。

接著我們利用我們開(kāi)頭的例子來(lái)理解一下這個(gè)FROM子句，很顯然我們的col4對(duì)應(yīng)上面的value_column,我們還假定列會(huì)下固定為這三項(xiàng)，那么列 col3 對(duì)應(yīng)上面的pivot_column,進(jìn)而我們應(yīng)該得出[abc],[abcd],[ae]是column_name即我們的輸出列，最后我們只要構(gòu)造一下table_source就可以了，如何構(gòu)造這個(gè)table_source，顯然pivot_column 和 value_column應(yīng)該包含在其中，其它就應(yīng)該是你想要分組的列啦.

我們來(lái)總結(jié)一下：這個(gè)FROM子句是基于 table_source 對(duì) pivot_column 進(jìn)行透視，table_source 中 pivot_column 和 value_column 列之外的列被稱(chēng)為透視運(yùn)算符的組合列,而PIVOT 是對(duì)輸入表執(zhí)行組合列的分組操作，并為每個(gè)組返回一行，好，我們?cè)囍鴮?xiě)出這個(gè)SQL：

 
 
 
  
  
  SELECT col1,col2,[abc],[abcd],[ae]  
  
  
  FROM  
  
  
  (SELECT col1,col2,col3,col4  
  
  
  FROM ta ) p  
  
  
  PIVOT  
  
  
  ( SUM (col4)  
  
  
  FOR col3 IN ([abc],[abcd],[ae])  
  
  
  )AS unpvt

我們執(zhí)行一下看看結(jié)果：

 
 
 
  
  
  /*  
  
  
  col1 abc abcd ae

以上的相關(guān)內(nèi)容就是對(duì)SQL Server 2005 的新功能的介紹，望你能有所收獲。

當(dāng)前名稱(chēng)：SQLServer2005新功能之PIVOT的描述
標(biāo)題鏈接：http://fisionsoft.com.cn/article/cdodisg.html

新聞中心

其他資訊