程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 動態行轉列

SQL Server 動態行轉列

日期:2017/1/9 14:45:19      編輯:關於SqlServer

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

      本文所涉及的內容(Contents)

      背景(Contexts)

      實現代碼(SQL Codes)

      方法一:使用拼接SQL,靜態列字段;

      方法二:使用拼接SQL,動態列字段;

      方法三:使用PIVOT關系運算符,靜態列字段;

      方法四:使用PIVOT關系運算符,動態列字段;

      二.背景(Contexts)

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

    SQL Server 動態行轉列 三聯

      (圖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]

    wps_clip_image-8842

      (圖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

    wps_clip_image-14456

      (圖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

    wps_clip_image-23886

      (圖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(['+@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)

    wps_clip_image-17757

      (圖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(['+@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)

    wps_clip_image-10798

      (圖6)

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved