行列轉(zhuǎn)換, 或者說(shuō)"樞轉(zhuǎn)", 可以使用pivot, unpivot實(shí)現(xiàn)

1. pivot 行轉(zhuǎn)列

pivot (行轉(zhuǎn)列)語(yǔ)法:

? ? select columns from table

? ? pivot[XML]( ? ? ?--XML是一個(gè)可選關(guān)鍵字, 使用XML格式輸出, 大量數(shù)據(jù)被轉(zhuǎn)化的話需要使用這個(gè)

? ? ? pivot_clause, ?--查詢的數(shù)據(jù), 一般是聚合函數(shù).因?yàn)樾枰闲修D(zhuǎn)列后的查詢邏輯

? ? ? pivot_for_clause, --定義對(duì)哪些列進(jìn)行分組和透視

? ? ? pivot_in_clause --過(guò)濾pivot_for_clause中的值, 每一個(gè)值都將作為一個(gè)單獨(dú)的列.

? ? )

準(zhǔn)備一些數(shù)據(jù), 如下:

一個(gè)典型的寫法:

? ? select * from students2

? ? pivot(

? ? ? sum(age) for name in('sdger','erw','wer')

? ? )

結(jié)果:

分析:

? ? ? 1. 使用 select * 通常被認(rèn)為不好, 但是一定要注意此時(shí)不能查詢一些與邏輯發(fā)生沖突的列, 例如: select id 可以, 但是select name, age 卻不行, 因?yàn)檎Z(yǔ)法本就是行轉(zhuǎn)列 (將name放到行, 數(shù)據(jù)是age), 盡量由pivot決定顯示的內(nèi)容.

? ? ? 2. 使用sum(age)不能使用age, 就如同使用group by一樣, 查詢的數(shù)據(jù)需要滿足劃分之后的邏輯.

? ? ? 3. 觀察上述結(jié)果, wer那一列, 它沒(méi)有發(fā)生sum()的計(jì)算, 原因很簡(jiǎn)單wer的id不同, 這說(shuō)明pivot默認(rèn)進(jìn)行全字段分組(這里是對(duì)id分了組再計(jì)算的sum).

? ? ? 4. null值表示沒(méi)有數(shù)據(jù), 比如沒(méi)有id=6, 同時(shí)name='sdger'的數(shù)據(jù), 不能顯示其sum(age).

那么, 如果想進(jìn)行指定分組字段, 例如想不要進(jìn)行id分組, 同一id的數(shù)據(jù)合并:

? ? select * from (select name,age from students2)?

? ? pivot(

? ? ? sum(age) for name in('sdger','erw','wer')

? ? )

--使用子查詢的方式, 提前約束處理的數(shù)據(jù)即可.

結(jié)果:

使用where:

? ? select * from students2

? ? pivot(

? ? ? ....

? ? )where ....

? ? 或者

? ? select .... from (select ... from table where ....)

? ? pivot(

? ? ? ....

? ? )

起別名:

執(zhí)行多個(gè)聚合:

按照多列分組:

使用XML進(jìn)行pivot:

XML數(shù)據(jù)展開(kāi)后如下:

另外:

2. unpivot 列轉(zhuǎn)行

unpivot (列轉(zhuǎn)行)語(yǔ)法:

? ? select * from table

? ? unpivot [include|exclude nulls](

? ? ? unpivot_clause ?--指定列名, 指定將來(lái)數(shù)據(jù)所在列的名稱

? ? ? unpivot_for_clause --指定列名, 指定列標(biāo)題轉(zhuǎn)化成行數(shù)據(jù)后所在列的名稱

? ? ? unpivot_in_clause --篩選哪些列需要轉(zhuǎn)化

? ? )

一個(gè)簡(jiǎn)單示例:

數(shù)據(jù)默認(rèn)不包含NULL值, 即exclude nulls.

如果要包含NUll值:

起別名:

**注意: 別名必須使用單引號(hào)否則報(bào)錯(cuò)**