蟲卜矢

在制作報表的時候，有時候會碰到基礎(chǔ)資料是依照時間區(qū)間去一筆一筆記錄的資料，但是使用者在看報表的時候想要將時間區(qū)間以橫向的方式呈現(xiàn)不是直向的情況出現(xiàn)，又或者基礎(chǔ)資料的表數(shù)據(jù)結(jié)構(gòu)是橫向的而使用者在看報表的時候想以直向方式呈現(xiàn)。這個時候我們就可以采用表旋轉(zhuǎn)技術(shù)。所謂的表旋轉(zhuǎn)，就是將表的行轉(zhuǎn)換為列，或是將表的列轉(zhuǎn)換為行.表旋轉(zhuǎn)在某些方面也是解決了表的數(shù)據(jù)存儲和實際需要之間的矛盾。

針對表旋轉(zhuǎn)技術(shù)從SQL Server2005開始 系統(tǒng)提供兩個新的關(guān)鍵字 PIVOT 和UNPIVOT可用來作表旋轉(zhuǎn)操作.因為這是從SQL Server2005開始提供的新關(guān)鍵字，所以如果希望使用此功能需要將數(shù)據(jù)庫的兼容級別設(shè)置為90。

PIVOT用于將列值旋轉(zhuǎn)為列名（即行轉(zhuǎn)列）、UNPIVOT用于將列明轉(zhuǎn)為列值（即列轉(zhuǎn)行）

PIVOT語法規(guī)則：

第一部分 SELECT .... FROM 之間:

<non-pivoted column> : 非旋轉(zhuǎn)列。

[first pivoted column] AS <column name> :旋轉(zhuǎn)列 AS <列名>

[first pivoted column] 的名稱將會對應(yīng)與之後第三部分的 FOR column IN 里面的的名稱。 

第二部分 FROM ..... PIVOT 之間:

(<SELECT query that produces the data>) AS <alias for the source query> : 這裡也就是你的資料來源的地方，要注意的是尾巴AS命名的名稱不可以漏掉一定要命名。 

第三部分 PIVOT 跟 FOR:

<aggregation function>(<column being aggregated>) :

<聚合函數(shù)>（<被聚合的列>）。

[<column that contains the values that will become column headers>] : [<包含將被轉(zhuǎn)換為列標(biāo)頭的值的列>]

IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) : 這部分前面有提到，這邊的列名稱對應(yīng)的是第一部分的名稱

UNPIVOT語法規(guī)則：

第一部分 SELECT .... FROM 之間:

<non-pivoted Rows> : 非旋轉(zhuǎn)行。

[colgroup_column name] :列集合的列名 稱將會對應(yīng)與之後第三部分的 FOR column IN 里面的的名稱而該列中的數(shù)據(jù)為IN后面的列名

[value set_column name]值集合的列名的名稱將會對應(yīng)與之後第三部分的 FOR 之前的的名稱，值集合數(shù)據(jù)是根據(jù)列分組組成集合！

第二部分 FROM ..... UNPIVOT 之間:

Table Source:表源

第三部分 UNPIVOT 跟 FOR:

[value set_column name] 值集合的列名這部分前面有提到，這邊的數(shù)據(jù)來源為根據(jù)[colgroup_column name]根據(jù)對應(yīng)列名分組的數(shù)據(jù)

[colgroup_column name] 列集合的列名這部分前面有提到，這邊的數(shù)據(jù)來源為根據(jù) IN關(guān)鍵字從Table Source表數(shù)據(jù)源中獲取的列的名稱

IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) :這邊的列名稱對應(yīng)的是Table Source中列的名稱也是

典型實例

一、     行轉(zhuǎn)列

建立一個產(chǎn)品銷售表，內(nèi)容包括員工姓名、銷售日期、銷售金額

代碼如下：

Create table Orders

(Staff_name  char(20) not null,

OrderDate  char(50)not null,

Amount    money  notnull

)

insert into Orders values('張三','Jan',150.00)

insert into Orders values('張三','Feb',100.00)

insert into Orders values('張三','Mar',100.00)

insert into Orders values('張三','Apr',150.00)

insert into Orders values('張三','May',100.00)

insert into Orders values('張三','Jun',170.00)

insert into Orders values('張三','Jul',100.00)

insert into Orders values('張三','Aug',110.00)

insert into Orders values('張三','Sep',100.00)

insert into Orders values('張三','Oct',180.00)

insert into Orders values('張三','Now',100.00)

insert into Orders values('張三','Dec',100.00)

 

select Staff_name,[Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec]

from(select Staff_name,OrderDate,Amount from orders) ct

pivot

(

sum(Amount)

for OrderDate in( [Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])

 

)as Pvt

order by Staff_name

注：使用pivot把一列拆成幾列時 需要后面as取個別名 這是固定的格式 同時如 for前是必須使用聚合函數(shù)的

當(dāng)然不使用pivot函數(shù)也可以得到相同效果 只是代碼長切效率低但容易理解

代碼如下：

select Staff_name,

sum(case when OrderDate=’Jan’ THEN Amount END) as [Jan],

sum(case when OrderDate=’Feb’ THEN Amount END) as [Feb],

sum(case when OrderDate=’Mar’ THEN Amount END) as [Mar],

sum(case when OrderDate=’Apr’ THEN Amount END) as [Jan],

sum(case when OrderDate=’May’ THEN Amount END) as [May],

sum(case when OrderDate=’Jun’ THEN Amount END) as [Jun],

sum(case when OrderDate=’Jul’ THEN Amount END) as [Jul],

sum(case when OrderDate=’Aug’ THEN Amount END) as [Aug],

sum(case when OrderDate=’Sep’ THEN Amount END) as [Sep],

sum(case when OrderDate=’Oct’ THEN Amount END) as [Oct],

sum(case when OrderDate=’Now’ THEN Amount END) as [Now],

sum(case when OrderDate=’Dec’ THEN Amount END) as [Dec]
from orders as a
group by Staff_name

轉(zhuǎn)換后結(jié)果：

二、    列轉(zhuǎn)行

沿用上面已經(jīng)轉(zhuǎn)換好的表結(jié)構(gòu)，其效果如下：

代碼如下：

select Staff_name ,OrderDate,Amount from orders2
unpivot
(
Amount for OrderDate in( [Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])

)as UnPvt

注：同樣需要使用as取別名同樣是固定的格式 unpivot函數(shù)中沒有聚合函數(shù) OrderDate和Amount列都是原來沒有的 Amount列由原來的[Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])列組成

請注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 會執(zhí)行一次聚合，從而將多個可能的行合并為輸出中的單個行。而 UNPIVOT 不會重現(xiàn)原始表值表達(dá)式的結(jié)果，因為行已經(jīng)被合并了。另外，UNPIVOT 的輸入中的 NULL 不會顯示在輸出中，然而在執(zhí)行 PIVOT 操作之前輸入中可能會含有原始的 NULL 值。

同樣的不使用unpivot也可以實現(xiàn)以上的功能

代碼如下：

select Staff_name, 'Jan' as OrderDate,Jan as Amount from orders

union all

select Staff_name, 'Feb' as OrderDate,Feb as Amount from orders

union all

select Staff_name, 'Mar' as OrderDate,Mar as Amount from orders

union all

select Staff_name, 'Apr' as OrderDate,Apr as Amount from orders

union all

select Staff_name, 'May' as OrderDate,May as Amount from orders

union all

select Staff_name, 'Jun' as OrderDate,Jun as Amount from orders

union all

select Staff_name, 'Jul' as OrderDate ,Jul as Amount from orders

union all

select Staff_name, 'Aug' as OrderDate,Aug as Amount from orders

union all

select Staff_name, 'Sep' as OrderDate,Sep as Amount from orders

union all

select Staff_name, 'Oct' as OrderDate ,Oct as Amount from orders

union all

select Staff_name, 'Now' as OrderDate ,Now as Amount from orders

union all

select Staff_name, 'Dec' as OrderDate ,Dec as Amount from orders

轉(zhuǎn)換后結(jié)果

通過上面示例大家可以看的出來，行轉(zhuǎn)列其實就是將表中的一行拆成幾列，而列轉(zhuǎn)行則是將表中的多個列組成一行