建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
一、使用PIVOT和UNPIVOT命令的SQL Server版本要求
創(chuàng)新互聯(lián)公司專(zhuān)業(yè)為企業(yè)提供豐縣網(wǎng)站建設(shè)、豐縣做網(wǎng)站、豐縣網(wǎng)站設(shè)計(jì)、豐縣網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、豐縣企業(yè)網(wǎng)站模板建站服務(wù)，十余年豐縣做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
1.數(shù)據(jù)庫(kù)的最低版本要求為SQL Server 2005 或更高。
2.必須將數(shù)據(jù)庫(kù)的兼容級(jí)別設(shè)置為90 或更高。
3.查看我的數(shù)據(jù)庫(kù)版本及兼容級(jí)別。
如果不知道怎么看數(shù)據(jù)庫(kù)版本或兼容級(jí)別的話(huà)可以在SQL Server Management Studio新建一個(gè)查詢(xún)窗口輸入：print @@version，運(yùn)行之后在我的本機(jī)上得到：
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.2
然后我們選擇一個(gè)數(shù)據(jù)庫(kù)然后右鍵-屬性 選擇[選項(xiàng)]得到下圖的信息。
在確認(rèn)數(shù)據(jù)庫(kù)的版本和兼容級(jí)別符合1,2點(diǎn)的要求后你才可以接著繼續(xù)往下學(xué)習(xí)。
二、使用PIVOT 實(shí)現(xiàn)數(shù)據(jù)表的列轉(zhuǎn)行
1.在這里我們先構(gòu)建一個(gè)測(cè)試數(shù)據(jù)表(這里使用的是臨時(shí)表，以方便我們?cè)谕顺鰰?huì)話(huà)的時(shí)候自動(dòng)刪除表及其數(shù)據(jù))
首先我們先設(shè)計(jì)一個(gè)表架構(gòu)為#Student { 學(xué)生編號(hào)[PK], 姓名, 性別, 所屬班級(jí) }的表,然后編寫(xiě)如下T-SQL
--創(chuàng)建臨時(shí)表(僅演示,表結(jié)構(gòu)的不合理還請(qǐng)包涵)
- CREATE TABLE #Student (
- [學(xué)生編號(hào)] INT IDENTITY(1, 1) PRIMARY KEY,
- [姓名] NVARCHAR(20),
- [性別] NVARCHAR(1),
- [所屬班級(jí)] NVARCHAR(20)
- );
--給臨時(shí)表插入數(shù)據(jù)
- INSERT INTO #Student (
- [姓名], [性別], [所屬班級(jí)]
- )
- SELECT '李妹妹', '女', '初一 1班' UNION ALL
- SELECT '泰強(qiáng)', '男', '初一 1班' UNION ALL
- SELECT '泰映', '男', '初一 1班' UNION ALL
- SELECT '何謝', '男', '初一 1班' UNION ALL
- SELECT '李春', '男', '初二 1班' UNION ALL
- SELECT '吳歌', '男', '初二 1班' UNION ALL
- SELECT '林純', '男', '初二 1班' UNION ALL
- SELECT '徐葉', '女', '初二 1班' UNION ALL
- SELECT '龍門(mén)', '男', '初三 1班' UNION ALL
- SELECT '小紅', '女', '初三 1班' UNION ALL
- SELECT '小李', '男', '初三 1班' UNION ALL
- SELECT '小黃', '女', '初三 2班' UNION ALL
- SELECT '旺財(cái)', '男', '初三 2班' UNION ALL
- SELECT '強(qiáng)強(qiáng)', '男', '初二 1班';
以下是查詢(xún)的結(jié)果：
|
學(xué)生編號(hào) |
姓名 |
性別 |
所屬班級(jí) |
|
1 |
李妹妹 |
女 |
初一 1班 |
|
2 |
泰強(qiáng) |
男 |
初一 1班 |
|
3 |
泰映 |
男 |
初一 1班 |
|
4 |
何謝 |
男 |
初一 1班 |
|
5 |
李春 |
男 |
初二 1班 |
|
6 |
吳歌 |
男 |
初二 1班 |
|
7 |
林純 |
男 |
初二 1班 |
|
8 |
徐葉 |
女 |
初二 1班 |
|
9 |
龍門(mén) |
男 |
初三 1班 |
|
10 |
小紅 |
女 |
初三 1班 |
|
11 |
小李 |
男 |
初三 1班 |
|
12 |
小黃 |
女 |
初三 2班 |
|
13 |
旺財(cái) |
男 |
初三 2班 |
|
14 |
強(qiáng)強(qiáng) |
男 |
初二 1班 |
2.查詢(xún)各班級(jí)的總?cè)藬?shù)
- SELECT
- [所屬班級(jí)] AS [班級(jí)],
- COUNT(1) AS [人數(shù)]
- FROM #Student
- GROUP BY [所屬班級(jí)]
- ORDER BY [人數(shù)] DESC
|
班級(jí) |
人數(shù) |
|
初二 1班 |
5 |
|
初一 1班 |
4 |
|
初三 1班 |
3 |
|
初三 2班 |
2 |
好了，在這里我希望把上面的表{ 班級(jí), 人數(shù) } 由 班級(jí)[行] 的顯示轉(zhuǎn)換為 班級(jí)[列] 的顯示格式!
在此你會(huì)看到第一個(gè)PIVOT示例。是否很期待??
3.編寫(xiě)第一個(gè)PIVOT示例
- SELECT
- '班級(jí)總?cè)藬?shù):' AS [總?cè)藬?shù)],
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- FROM (
- SELECT
- [所屬班級(jí)] AS [班級(jí)],
- [學(xué)生編號(hào)]
- FROM #Student
- ) AS [SourceTable]
- PIVOT (
- COUNT([學(xué)生編號(hào)])
- FOR [班級(jí)] IN (
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- )
- ) AS [PivotTable]
在結(jié)果表中我們看到了對(duì)于不存在的班級(jí)初一2班它的總?cè)藬?shù)為0，這符合我們預(yù)期的結(jié)果!
解釋?zhuān)菏褂肞OVIT首先你需要在FROM子句內(nèi)定義2個(gè)表：
A.一個(gè)稱(chēng)為源表(SourceTable)。
B.另一個(gè)稱(chēng)為數(shù)據(jù)透視表(PivotTable)。
語(yǔ)法：
- SELECT
- <未透視的列>,
- [第一個(gè)透視列] AS <列別名>,
- [第二個(gè)透視列] AS <列別名>,
- ...
- [最后一個(gè)透視列] AS <列別名>
- FROM (
- ) AS <源表>
- PIVOT (
- <聚合函數(shù)>(<列>)
- FOR [<需要轉(zhuǎn)換為行的列>] IN (
- [第一個(gè)透視列], [第二個(gè)透視列],
- ...
- [最后一個(gè)透視列]
- )
- ) AS <數(shù)據(jù)透視表>
- <可選的ORDER BY子句>;
以上的PIVOT子句內(nèi)的第1…n個(gè)透視列的值均為需要轉(zhuǎn)換為行的列的常量值,需要用[]括起，支持GUID，字符串及各種數(shù)字!
4.下面演示一個(gè)較為高級(jí)的行轉(zhuǎn)列的應(yīng)用示例
--使用PIVOT查詢(xún)班級(jí)內(nèi)的男女學(xué)生人數(shù)及總?cè)藬?shù)
- SELECT
- [所屬班級(jí)] AS [班級(jí)],
- [男] AS [男生人數(shù)],
- [女] AS [女生人數(shù)],
- [男] + [女] AS [總?cè)藬?shù)]
- FROM (
- SELECT [學(xué)生編號(hào)], [所屬班級(jí)], [性別] FROM #Student
- ) AS [SourceTable]
- PIVOT (
- COUNT([學(xué)生編號(hào)])
- FOR [性別] IN (
- [男], [女]
- )
- ) AS [PivotTable]
- ORDER BY [總?cè)藬?shù)] DESC
#p#
三、使用UNPIVOT 實(shí)現(xiàn)的功能其實(shí)與PIVOT恰恰相反
1.語(yǔ)法同PIVOT但是UNPIVOT的子句沒(méi)有聚合函數(shù)
- SELECT
- <未逆透視的列>,
- [合并后的列] AS <列別名>,
- [行值的列名] AS <列別名>
- FROM (
- ) AS <源表>
- UNPIVOT (
- <行值的列名>
- FOR <將原來(lái)多個(gè)列合并到單個(gè)列的列名> IN (
- [第一個(gè)合并列], [第二個(gè)合并列],
- ...
- [最后一個(gè)合并列]
- )
- ) AS <數(shù)據(jù)逆透視表>
- <可選的ORDER BY子句>;
2.看上面的語(yǔ)法感覺(jué)很浮云，不怕，這里帶例子(繼續(xù)使用II中用到的PIVOT表)
--源表
- SELECT
- '班級(jí)總?cè)藬?shù):' AS [總?cè)藬?shù)],
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- INTO #PivotTable --為了使表達(dá)意圖更清晰,我把PIVOT處理后的表放到一個(gè)臨時(shí)表當(dāng)中
- FROM (
- SELECT
- [所屬班級(jí)] AS [班級(jí)],
- [學(xué)生編號(hào)]
- FROM #Student
- ) AS [SourceTable]
- PIVOT (
- COUNT([學(xué)生編號(hào)])
- FOR [班級(jí)] IN (
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- )
- ) AS [PivotTable]
將多個(gè)列合并到單個(gè)列的轉(zhuǎn)換的語(yǔ)句!!!
--結(jié)果
- SELECT
- [班級(jí)], [總?cè)藬?shù)]
- FROM (
- SELECT
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- FROM
- #PivotTable
- ) AS [s]
- UNPIVOT (
- [總?cè)藬?shù)]
- FOR [班級(jí)] IN (
- [初一 1班], [初一 2班],
- [初二 1班],
- [初三 1班], [初三 2班]
- )
- ) AS [un_p]
執(zhí)行下面代碼：
- SELECT
- [所屬班級(jí)] AS [班級(jí)],
- [男] AS [男生人數(shù)],
- [女] AS [女生人數(shù)],
- [男] + [女] AS [總?cè)藬?shù)]
- INTO #PivotTable2 --放到臨時(shí)表方便查詢(xún)
- FROM (
- SELECT [學(xué)生編號(hào)], [所屬班級(jí)], [性別] FROM #Student
- ) AS [SourceTable]
- PIVOT (
- COUNT([學(xué)生編號(hào)])
- FOR [性別] IN (
- [男], [女]
- )
- ) AS [PivotTable]
- ORDER BY [總?cè)藬?shù)] DESC
- SELECT
- [班級(jí)],
- [男生或女生人數(shù)],
- [性別],
- [總?cè)藬?shù)]
- FROM (
- SELECT [班級(jí)], [男生人數(shù)], [女生人數(shù)], [總?cè)藬?shù)] FROM #PivotTable2
- ) AS [s]
- UNPIVOT (
- [男生或女生人數(shù)]
- FOR [性別] IN (
- [男生人數(shù)],
- [女生人數(shù)]
- )
- ) AS [un_p]
或者將性別和人數(shù)合并到一個(gè)列當(dāng)中：
- SELECT
- [班級(jí)],
- [性別] + ': ' + CAST([男生或女生人數(shù)] AS NVARCHAR(1)) AS [男生或女生人數(shù)],
- [總?cè)藬?shù)]
- FROM (
- SELECT [班級(jí)], [男生人數(shù)], [女生人數(shù)], [總?cè)藬?shù)] FROM #PivotTable2
- ) AS [s]
- UNPIVOT (
- [男生或女生人數(shù)]
- FOR [性別] IN (
- [男生人數(shù)],
- [女生人數(shù)]
- )
- ) AS [un_p]
關(guān)于PIVOT和UNPIVOT命令的使用就介紹到這里，如果想了解更多SQL的知識(shí)可以去看看這里的文章：http://database./sqlserver/，絕對(duì)不會(huì)讓您失望的哦！
分享標(biāo)題：T-SQL行列相互轉(zhuǎn)換命令：PIVOT和UNPIVOT使用詳解
標(biāo)題路徑：http://m.fisionsoft.com.cn/article/ccescos.html
