一.本文所涉及的內容(Contents)
二.背景(Contexts)
其實行轉列并不是一個什么新鮮的話題了,甚至已經被大家說到爛了,網上的很多例子多多少少都有些問題,所以我希望能讓大家快速的看到執行的效果,所以在動態列的基礎上再把表、分組字段、行轉列字段、值這四個行轉列固定需要的值變成真正意義的參數化,大家只需要根據自己的環境,設置參數值,馬上就能看到效果了(可以直接跳轉至:“ 參數化動態PIVOT行轉列 ”查看具體的腳本代碼)。行轉列的效果圖如圖1所示:
(圖1:行轉列效果圖)
三.實現代碼(SQL Codes)
(一) 首先我們先創建一個測試表,往里面插入測試數據,返回表記錄如圖2所示:
-- 創建測試表 IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N ' [dbo].[TestRows2Columns] ' ) AND type in (N ' U ' )) DROP TABLE [ dbo ] . [ TestRows2Columns ] GO CREATE TABLE [ dbo ] . [ TestRows2Columns ] ( [ Id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ UserName ] [ nvarchar ] ( 50 ) NULL , [ Subject ] [ nvarchar ] ( 50 ) NULL , [ Source ] [ numeric ] ( 18 , 0 ) NULL ) ON [ PRIMARY ] GO -- 插入測試數據 INSERT INTO [ TestRows2Columns ] ( [ UserName ] , [ Subject ] , [ Source ] ) SELECT N ' 張三 ' ,N ' 語文 ' , 60 UNION ALL SELECT N ' 李四 ' ,N ' 數學 ' , 70 UNION ALL SELECT N ' 王五 ' ,N ' 英語 ' , 80 UNION ALL SELECT N ' 王五 ' ,N ' 數學 ' , 75 UNION ALL SELECT N ' 王五 ' ,N ' 語文 ' , 57 UNION ALL SELECT N ' 李四 ' ,N ' 語文 ' , 80 UNION ALL SELECT N ' 張三 ' ,N ' 英語 ' , 100 GO SELECT * FROM [ TestRows2Columns ]
(圖2:樣本數據)
-- 1:靜態拼接行轉列 SELECT [ UserName ] , SUM ( CASE [ Subject ] WHEN ' 數學 ' THEN [ Source ] ELSE 0 END ) AS ' [數學] ' , SUM ( CASE [ Subject ] WHEN ' 英語 ' THEN [ Source ] ELSE 0 END ) AS ' [英語] ' , SUM ( CASE [ Subject ] WHEN ' 語文 ' THEN [ Source ] ELSE 0 END ) AS ' [語文] ' FROM [ TestRows2Columns ] GROUP BY [ UserName ] GO
(圖3:樣本數據)
(三) 接著以動態的方式實現行轉列,這是使用拼接SQL的方式實現的,所以它適用于SQL Server 2000以上的數據庫版本,執行腳本返回的結果如圖2所示;
-- 2:動態拼接行轉列 DECLARE @sql VARCHAR ( 8000 ) SET @sql = ' SELECT [UserName], ' SELECT @sql = @sql + ' SUM(CASE [Subject] WHEN ''' + [ Subject ] + ''' THEN [Source] ELSE 0 END) AS ''' + QUOTENAME ( [ Subject ] ) + ''' , ' FROM ( SELECT DISTINCT [ Subject ] FROM [ TestRows2Columns ] ) AS a SELECT @sql = LEFT ( @sql , LEN ( @sql ) - 1 ) + ' FROM [TestRows2Columns] GROUP BY [UserName] ' PRINT ( @sql ) EXEC ( @sql ) GO
(四) 在SQL Server 2005之后有了一個專門的PIVOT 和 UNPIVOT 關系運算符做行列之間的轉換,下面是靜態的方式實現的,實現效果如圖4所示:
-- 3:靜態PIVOT行轉列 SELECT * FROM ( SELECT [ UserName ] , [ Subject ] , [ Source ] FROM [ TestRows2Columns ] ) p PIVOT ( SUM ( [ Source ] ) FOR [ Subject ] IN ( [ 數學 ] , [ 英語 ] , [ 語文 ] ) ) AS pvt ORDER BY pvt. [ UserName ] ; GO
(圖4)
(五) 把上面靜態的SQL基礎上進行修改,這樣就不用理會記錄里面存儲了什么,需要轉成什么列名的問題了,腳本如下,效果如圖4所示:
-- 4:動態PIVOT行轉列 DECLARE @sql_str VARCHAR ( 8000 ) DECLARE @sql_col VARCHAR ( 8000 ) SELECT @sql_col = ISNULL ( @sql_col + ' , ' , '' ) + QUOTENAME ( [ Subject ] ) FROM [ TestRows2Columns ] GROUP BY [ Subject ] SET @sql_str = ' SELECT * FROM ( SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT (SUM([Source]) FOR [Subject] IN ( ' + @sql_col + ' ) ) AS pvt ORDER BY pvt.[UserName] ' PRINT ( @sql_str ) EXEC ( @sql_str )
(六) 也許很多人到了上面一步就夠了,但是你會發現,當別人拿到你的代碼,需要不斷的修改成他自己環境中表名、分組列、行轉列字段、字段值這幾個參數,邏輯如圖5所示,所以,我繼續對上面的腳本進行修改,你只要設置自己的參數就可以實現行轉列了,效果如圖4所示:
-- 5:參數化動態PIVOT行轉列 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2014.05.26> -- Description: <參數化動態PIVOT行轉列> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= DECLARE @sql_str NVARCHAR ( MAX ) DECLARE @sql_col NVARCHAR ( MAX ) DECLARE @tableName SYSNAME -- 行轉列表 DECLARE @groupColumn SYSNAME -- 分組字段 DECLARE @row2column SYSNAME -- 行變列的字段 DECLARE @row2columnValue SYSNAME -- 行變列值的字段 SET @tableName = ' TestRows2Columns ' SET @groupColumn = ' UserName ' SET @row2column = ' Subject ' SET @row2columnValue = ' Source ' -- 從行數據中獲取可能存在的列 SET @sql_str = N ' SELECT @sql_col_out = ISNULL(@sql_col_out + '' , '' , '''' ) + QUOTENAME([ ' + @row2column + ' ]) FROM [ ' + @tableName + ' ] GROUP BY [ ' + @row2column + ' ] ' -- PRINT @sql_str EXEC sp_executesql @sql_str ,N ' @sql_col_out NVARCHAR(MAX) OUTPUT ' , @sql_col_out = @sql_col OUTPUT -- PRINT @sql_col SET @sql_str = N ' SELECT * FROM ( SELECT [ ' + @groupColumn + ' ],[ ' + @row2column + ' ],[ ' + @row2columnValue + ' ] FROM [ ' + @tableName + ' ]) p PIVOT (SUM([ ' + @row2columnValue + ' ]) FOR [ ' + @row2column + ' ] IN ( ' + @sql_col + ' ) ) AS pvt ORDER BY pvt.[ ' + @groupColumn + ' ] ' -- PRINT (@sql_str) EXEC ( @sql_str )
(圖5)
(七) 在實際的運用中,我經常遇到需要對基礎表的數據進行篩選后再進行行轉列,那么下面的腳本將滿足你這個需求,效果如圖6所示:
-- 6:帶條件查詢的參數化動態PIVOT行轉列 -- ============================================= -- Author: <聽風吹雨> -- Create date: <2014.05.26> -- Description: <參數化動態PIVOT行轉列,帶條件查詢的參數化動態PIVOT行轉列> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= DECLARE @sql_str NVARCHAR ( MAX ) DECLARE @sql_col NVARCHAR ( MAX ) DECLARE @sql_where NVARCHAR ( MAX ) DECLARE @tableName SYSNAME -- 行轉列表 DECLARE @groupColumn SYSNAME -- 分組字段 DECLARE @row2column SYSNAME -- 行變列的字段 DECLARE @row2columnValue SYSNAME -- 行變列值的字段 SET @tableName = ' TestRows2Columns ' SET @groupColumn = ' UserName ' SET @row2column = ' Subject ' SET @row2columnValue = ' Source ' SET @sql_where = ' WHERE UserName = '' 王五 ''' -- 從行數據中獲取可能存在的列 SET @sql_str = N ' SELECT @sql_col_out = ISNULL(@sql_col_out + '' , '' , '''' ) + QUOTENAME([ ' + @row2column + ' ]) FROM [ ' + @tableName + ' ] ' + @sql_where + ' GROUP BY [ ' + @row2column + ' ] ' -- PRINT @sql_str EXEC sp_executesql @sql_str ,N ' @sql_col_out NVARCHAR(MAX) OUTPUT ' , @sql_col_out = @sql_col OUTPUT -- PRINT @sql_col SET @sql_str = N ' SELECT * FROM ( SELECT [ ' + @groupColumn + ' ],[ ' + @row2column + ' ],[ ' + @row2columnValue + ' ] FROM [ ' + @tableName + ' ] ' + @sql_where + ' ) p PIVOT (SUM([ ' + @row2columnValue + ' ]) FOR [ ' + @row2column + ' ] IN ( ' + @sql_col + ' ) ) AS pvt ORDER BY pvt.[ ' + @groupColumn + ' ] ' -- PRINT (@sql_str) EXEC ( @sql_str )
(圖6)
四.參考文獻(References)
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
