SQL Server 動態行轉列（參數化表名、分組列、行轉列欄位、欄位值）

来源:http://www.cnblogs.com/cykj/archive/2017/05/11/SQLServer-RowCol.html
-Advertisement-
Play Games

SQL Server 動態行轉列（參數化表名、分組列、行轉列欄位、欄位值） ...


一.本文所涉及的內容（Contents）

本文所涉及的內容（Contents）
背景（Contexts）
實現代碼（SQL Codes）
方法一：使用拼接SQL，靜態列欄位；
方法二：使用拼接SQL，動態列欄位；
方法三：使用PIVOT關係運算符，靜態列欄位；
方法四：使用PIVOT關係運算符，動態列欄位；
擴展閱讀一：參數化表名、分組列、行轉列欄位、欄位值；
擴展閱讀二：在前面的基礎上加入條件過濾；
參考文獻（References）

二.背景（Contexts）

其實行轉列並不是一個什麼新鮮的話題了，甚至已經被大家說到爛了，網上的很多例子多多少少都有些問題，所以我希望能讓大家快速的看到執行的效果，所以在動態列的基礎上再把表、分組欄位、行轉列欄位、值這四個行轉列固定需要的值變成真正意義的參數化，大家只需要根據自己的環境，設置參數值，馬上就能看到效果了（可以直接跳轉至：“參數化動態PIVOT行轉列”查看具體的腳本代碼）。行轉列的效果圖如圖1所示：

（圖1：行轉列效果圖）

三.實現代碼（SQL Codes）

(一) 首先我們先創建一個測試表，往裡面插入測試數據，返回表記錄如圖2所示：

 1 --創建測試表
 2 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))
 3 DROP TABLE [dbo].[TestRows2Columns]
 4 GO
 5 CREATE TABLE [dbo].[TestRows2Columns](
 6     [Id] [int] IDENTITY(1,1) NOT NULL,
 7     [UserName] [nvarchar](50) NULL,
 8     [Subject] [nvarchar](50) NULL,
 9     [Source] [numeric](18, 0) NULL
10 ) ON [PRIMARY]
11 GO
12 
13 --插入測試數據
14 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) 
15     SELECT N'張三',N'語文',60  UNION ALL
16     SELECT N'李四',N'數學',70  UNION ALL
17     SELECT N'王五',N'英語',80  UNION ALL
18     SELECT N'王五',N'數學',75  UNION ALL
19     SELECT N'王五',N'語文',57  UNION ALL
20     SELECT N'李四',N'語文',80  UNION ALL
21     SELECT N'張三',N'英語',100
22 GO
23 
24 SELECT * FROM [TestRows2Columns]

（圖2：樣本數據）

(二) 先以靜態的方式實現行轉列，效果如圖3所示：

1 --1：靜態拼接行轉列
2 SELECT [UserName],
3 SUM(CASE [Subject] WHEN '數學' THEN [Source] ELSE 0 END) AS '[數學]',
4 SUM(CASE [Subject] WHEN '英語' THEN [Source] ELSE 0 END) AS '[英語]',
5 SUM(CASE [Subject] WHEN '語文' THEN [Source] ELSE 0 END) AS '[語文]'     
6 FROM [TestRows2Columns]
7 GROUP BY [UserName]
8 GO

 

（圖3：樣本數據）

(三) 接著以動態的方式實現行轉列，這是使用拼接SQL的方式實現的，所以它適用於SQL Server 2000以上的資料庫版本，執行腳本返回的結果如圖2所示；

1 --2：動態拼接行轉列
2 DECLARE @sql VARCHAR(8000)
3 SET @sql = 'SELECT [UserName],'   
4 SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','   
5 FROM (SELECT DISTINCT [Subject] FROM [TestRows2Columns]) AS a     
6 SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ' FROM [TestRows2Columns] GROUP BY [UserName]'   
7 PRINT(@sql)
8 EXEC(@sql)
9 GO

 

(四) 在SQL Server 2005之後有了一個專門的PIVOT 和 UNPIVOT 關係運算符做行列之間的轉換，下麵是靜態的方式實現的，實現效果如圖4所示：

 1 --3：靜態PIVOT行轉列
 2 SELECT  *
 3 FROM    ( SELECT    [UserName] ,
 4                     [Subject] ,
 5                     [Source]
 6           FROM      [TestRows2Columns]
 7         ) p PIVOT
 8 ( SUM([Source]) FOR [Subject] IN ( [數學],[英語],[語文] ) ) AS pvt
 9 ORDER BY pvt.[UserName];
10 GO

（圖4）

(五) 把上面靜態的SQL基礎上進行修改，這樣就不用理會記錄裡面存儲了什麼，需要轉成什麼列名的問題了，腳本如下，效果如圖4所示：

 1 --4：動態PIVOT行轉列
 2 DECLARE @sql_str VARCHAR(8000)
 3 DECLARE @sql_col VARCHAR(8000)
 4 SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([Subject]) FROM [TestRows2Columns] GROUP BY [Subject]
 5 SET @sql_str = '
 6 SELECT * FROM (
 7     SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT 
 8     (SUM([Source]) FOR [Subject] IN ( '+ @sql_col +') ) AS pvt 
 9 ORDER BY pvt.[UserName]'
10 PRINT (@sql_str)
11 EXEC (@sql_str)

 

(六) 也許很多人到了上面一步就夠了，但是你會發現，當別人拿到你的代碼，需要不斷的修改成他自己環境中表名、分組列、行轉列欄位、欄位值這幾個參數，邏輯如圖5所示，

 1 --5：參數化動態PIVOT行轉列
 2 -- =============================================
 3 -- Author:        <聽風吹雨>
 4 -- Create date: <2014.05.26>
 5 -- Description:    <參數化動態PIVOT行轉列>
 6 -- Blog:        <http://www.cnblogs.com/gaizai/>
 7 -- =============================================
 8 DECLARE @sql_str NVARCHAR(MAX)
 9 DECLARE @sql_col NVARCHAR(MAX)
10 DECLARE @tableName SYSNAME --行轉列表
11 DECLARE @groupColumn SYSNAME --分組欄位
12 DECLARE @row2column SYSNAME --行變列的欄位
13 DECLARE @row2columnValue SYSNAME --行變列值的欄位
14 SET @tableName = 'TestRows2Columns'
15 SET @groupColumn = 'UserName'
16 SET @row2column = 'Subject'
17 SET @row2columnValue = 'Source'
18 
19 --從行數據中獲取可能存在的列
20 SET @sql_str = N'
21 SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+']) 
22     FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
23 --PRINT @sql_str
24 EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
25 --PRINT @sql_col
26 
27 SET @sql_str = N'
28 SELECT * FROM (
29     SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT 
30     (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt 
31 ORDER BY pvt.['+@groupColumn+']'
32 --PRINT (@sql_str)
33 EXEC (@sql_str)

（圖5）

所以，我繼續對上面的腳本進行修改，你只要設置自己的參數就可以實現行轉列了，效果如圖4所示：

(七) 在實際的運用中，我經常遇到需要對基礎表的數據進行篩選後再進行行轉列，那麼下麵的腳本將滿足你這個需求，效果如圖6所示：

 

 1 --6：帶條件查詢的參數化動態PIVOT行轉列
 2 -- =============================================
 3 -- Author:        <聽風吹雨>
 4 -- Create date: <2014.05.26>
 5 -- Description:    <參數化動態PIVOT行轉列，帶條件查詢的參數化動態PIVOT行轉列>
 6 -- Blog:        <http://www.cnblogs.com/gaizai/>
 7 -- =============================================
 8 DECLARE @sql_str NVARCHAR(MAX)
 9 DECLARE @sql_col NVARCHAR(MAX)
10 DECLARE @sql_where NVARCHAR(MAX)
11 DECLARE @tableName SYSNAME --行轉列表
12 DECLARE @groupColumn SYSNAME --分組欄位
13 DECLARE @row2column SYSNAME --行變列的欄位
14 DECLARE @row2columnValue SYSNAME --行變列值的欄位
15 SET @tableName = 'TestRows2Columns'
16 SET @groupColumn = 'UserName'
17 SET @row2column = 'Subject'
18 SET @row2columnValue = 'Source'
19 SET @sql_where = 'WHERE UserName = ''王五'''
20 
21 --從行數據中獲取可能存在的列
22 SET @sql_str = N'
23 SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+']) 
24     FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'
25 --PRINT @sql_str
26 EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
27 --PRINT @sql_col
28 
29 SET @sql_str = N'
30 SELECT * FROM (
31     SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']'+@sql_where+') p PIVOT 
32     (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt 
33 ORDER BY pvt.['+@groupColumn+']'
34 --PRINT (@sql_str)
35 EXEC (@sql_str)

（圖6）

四.參考文獻（References）

使用 PIVOT 和 UNPIVOT

註：本文轉載自 聽風吹雨 網址：http://www.cnblogs.com/gaizai/p/3753296.html#_labelFive


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • IOS 被拒 關於 iPhone running iOS 10.3.1 on Wi-Fi connected to an IPv6 network.
    問題： Guideline 2.1 - Performance Thank you for your resubmission. However, we discovered one or more bugs in your app when reviewed on iPhone running i ...
  • Apple本地認證(密碼+Touch id)
    本片博客主要介紹如何在自己的APP中添加指紋解鎖/密碼解鎖技術。主要是對蘋果的LocalAuthentication的介紹。看過之後你將會知道如何使用蘋果的指紋解鎖和本地密碼驗證功能。 ...
  • 解決關於 在android studio 出現的 DELETE_FAILED_INTERNAL_ERROR Error while Installing APK 問題
    在ionic2開發中，用android studio 打包apk的時候出現DELETE_FAILED_INTERNAL_ERROR Error while Installing APK。 我的android studio 版本是 解決方案： Android Studio > Settings> Bu ...
  • Mysql 啟動失敗常見錯誤
    各位可以按照順序逐條拍錯。 mysql啟動時報錯：Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql-master/data/mysql.pid) 的解決方法： 1、可能是/opt/mysql- ...
  • 大文件資料庫腳本導入解決方案
    當客戶伺服器不允許直接備份時，往往通過導出資料庫腳本的方式來部署-還原資料庫， 但是當資料庫導出腳本很大，用Microsoft SQL Server Management Studio執行腳本時，往往會遇到“記憶體不足”的提示。 解決辦法： 用微軟自帶的sqlcmd工具，可以導入執行。以SQL Ser ...
  • Atlas+Keepalived實現MySQL讀寫分離、讀負載均衡
    一、基礎介紹 1、背景描述 目前我們的高可用DB的代理層採用的是360開源的Atlas，從上線以來，已穩定運行2個多月。無論是從性能上，還是穩定性上，相比其他開源組件（amoeba、cobar、MaxScale、MySQL-Proxy等），還是很出色的。 當初我們之所以選擇Atlas，主要看中它有以 ...
  • oracle分區交換技術
    交換分區的操作步驟如下：1. 創建分區表t1，假設有2個分區，P1，P2.2. 創建基表t11存放P1規則的數據。3. 創建基表t12 存放P2規則的數據。4. 用基表t11和分區表T1的P1分區交換。 把表t11的數據放到到P1分區5. 用基表t12 和分區表T1p2 分區交換。 把表t12的數據 ...
  • linux下mysql多實例安裝
    1.MySQL多實例介紹 1.1.什麼是MySQL多實例 MySQL多實例就是在一臺機器上開啟多個不同的服務埠（如：3306,3307），運行多個MySQL服務進程，通過不同的socket監聽不同的服務埠來提供各自的服務:； 1.2.MySQL多實例的特點有以下幾點 1：有效利用伺服器資源，當單 ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類