其實(shí)行轉列并不是一個(gè)什么新鮮的話(huà)題了，甚至已經(jīng)被大家說(shuō)到爛了，網(wǎng)上的很多例子多多少少都有些問(wèn)題，所以我希望能讓大家快速的看到執行的效果，所以在動(dòng)態(tài)列的基礎上再把表、分組字段、行轉列字段、值這四個(gè)行轉列固定需要的值變成真正意義的參數化，大家只需要根據自己的環(huán)境，設置參數值，馬上就能看到效果了（可以直接跳轉至：“參數化動(dòng)態(tài)PIVOT行轉列”查看具體的腳本代碼）。行轉列的效果圖如圖1所示：
（圖1：行轉列效果圖）
(一) 首先我們先創(chuàng )建一個(gè)測試表，往里面插入測試數據，返回表記錄如圖2所示：
--創(chuàng )建測試表IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE [dbo].[TestRows2Columns]GOCREATE 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'語(yǔ)文',60 UNION ALL SELECT N'李四',N'數學(xué)',70 UNION ALL SELECT N'王五',N'英語(yǔ)',80 UNION ALL SELECT N'王五',N'數學(xué)',75 UNION ALL SELECT N'王五',N'語(yǔ)文',57 UNION ALL SELECT N'李四',N'語(yǔ)文',80 UNION ALL SELECT N'張三',N'英語(yǔ)',100GOSELECT * FROM [TestRows2Columns]
（圖2：樣本數據）
(二) 先以靜態(tài)的方式實(shí)現行轉列，效果如圖3所示：
--1：靜態(tài)拼接行轉列SELECT [UserName],SUM(CASE [Subject] WHEN '數學(xué)' THEN [Source] ELSE 0 END) AS '[數學(xué)]',SUM(CASE [Subject] WHEN '英語(yǔ)' THEN [Source] ELSE 0 END) AS '[英語(yǔ)]',SUM(CASE [Subject] WHEN '語(yǔ)文' THEN [Source] ELSE 0 END) AS '[語(yǔ)文]' FROM [TestRows2Columns]GROUP BY [UserName]GO
（圖3：樣本數據）
(三) 接著(zhù)以動(dòng)態(tài)的方式實(shí)現行轉列，這是使用拼接SQL的方式實(shí)現的，所以它適用于SQL Server 2000以上的數據庫版本，執行腳本返回的結果如圖2所示；
--2：動(dòng)態(tài)拼接行轉列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之后有了一個(gè)專(zhuān)門(mén)的PIVOT 和 UNPIVOT 關(guān)系運算符做行列之間的轉換，下面是靜態(tài)的方式實(shí)現的，實(shí)現效果如圖4所示：
--3：靜態(tài)PIVOT行轉列SELECT *FROM ( SELECT [UserName] , [Subject] , [Source] FROM [TestRows2Columns] ) p PIVOT( SUM([Source]) FOR [Subject] IN ( [數學(xué)],[英語(yǔ)],[語(yǔ)文] ) ) AS pvtORDER BY pvt.[UserName];GO
（圖4）
(五) 把上面靜態(tài)的SQL基礎上進(jìn)行修改，這樣就不用理會(huì )記錄里面存儲了什么，需要轉成什么列名的問(wèn)題了，腳本如下，效果如圖4所示：
--4：動(dòng)態(tài)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)
(六) 也許很多人到了上面一步就夠了，但是你會(huì )發(fā)現，當別人拿到你的代碼，需要不斷的修改成他自己環(huán)境中表名、分組列、行轉列字段、字段值這幾個(gè)參數，邏輯如圖5所示，所以，我繼續對上面的腳本進(jìn)行修改，你只要設置自己的參數就可以實(shí)現行轉列了，效果如圖4所示：
--5：參數化動(dòng)態(tài)PIVOT行轉列-- =============================================-- Author: <聽(tīng)風(fēng)吹雨>-- Create date: <2014.05.26>-- Description: <參數化動(dòng)態(tài)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_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT--PRINT @sql_colSET @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）
(七) 在實(shí)際的運用中，我經(jīng)常遇到需要對基礎表的數據進(jìn)行篩選后再進(jìn)行行轉列，那么下面的腳本將滿(mǎn)足你這個(gè)需求，效果如圖6所示：
--6：帶條件查詢(xún)的參數化動(dòng)態(tài)PIVOT行轉列-- =============================================-- Author: <聽(tīng)風(fēng)吹雨>-- Create date: <2014.05.26>-- Description: <參數化動(dòng)態(tài)PIVOT行轉列，帶條件查詢(xún)的參數化動(dòng)態(tài)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_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT--PRINT @sql_colSET @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）
聯(lián)系客服
