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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類