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

標籤：

 　　一.本文所涉及的內容(Contents)

　　本文所涉及的內容(Contents)

　　背景(Contexts)

　　實現代碼(SQL Codes)

　　方法一：使用拼接SQL，靜態欄欄位;

　　方法二：使用拼接SQL，動態欄欄位;

　　方法三：使用PIVOT關係運算子，靜態欄欄位;

　　方法四：使用PIVOT關係運算子，動態欄欄位;

　　二.背景(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：樣本資料)

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

　　--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:

　　-- =============================================

　　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([‘[email protected]+‘])

　　FROM [‘[email protected]+‘] GROUP BY [‘[email protected]+‘]‘

　　--PRINT @sql_str

　　EXEC sp_executesql @sql_str,N‘@sql_col_out NVARCHAR(MAX) OUTPUT‘,@[email protected]_col OUTPUT

　　--PRINT @sql_col

　　SET @sql_str = N‘

　　SELECT * FROM (

　　SELECT [‘[email protected]+‘],[‘[email protected]+‘],[‘[email protected]+‘] FROM [‘[email protected]+‘]) p PIVOT

　　(SUM([‘[email protected]+‘]) FOR [‘[email protected]+‘] IN ( ‘+ @sql_col +‘) ) AS pvt

　　ORDER BY pvt.[‘[email protected]+‘]‘

　　--PRINT (@sql_str)

　　EXEC (@sql_str)

　　(圖5)

　　(七) 在實際的運用中，我經常遇到需要對基礎資料表的資料進行篩選後再進行行轉列，那麼下面的指令碼將滿足你這個需求，效果6所示：

　　--6：帶條件查詢的參數化動態PIVOT行轉列

　　-- =============================================

　　-- Author: <聽風吹雨>

　　-- Create date: <2014.05.26>

　　-- Description: <參數化動態PIVOT行轉列，帶條件查詢的參數化動態PIVOT行轉列>

　　-- Blog:

　　-- =============================================

　　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([‘[email protected]+‘])

　　FROM [‘[email protected]+‘] ‘[email protected]_where+‘ GROUP BY [‘[email protected]+‘]‘

　　--PRINT @sql_str

　　EXEC sp_executesql @sql_str,N‘@sql_col_out NVARCHAR(MAX) OUTPUT‘,@[email protected]_col OUTPUT

　　--PRINT @sql_col

　　SET @sql_str = N‘

　　SELECT * FROM (

　　SELECT [‘[email protected]+‘],[‘[email protected]+‘],[‘[email protected]+‘] FROM [‘[email protected]+‘]‘[email protected]_where+‘) p PIVOT

　　(SUM([‘[email protected]+‘]) FOR [‘[email protected]+‘] IN ( ‘+ @sql_col +‘) ) AS pvt

　　ORDER BY pvt.[‘[email protected]+‘]‘

　　--PRINT (@sql_str)

　　EXEC (@sql_str)

　　(圖6)

  轉自：http://www.cnblogs.com/gaizai/p/3753296.html

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