亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL Server 動態行轉列(參數化表名、分組列、

系統 1801 0
原文: SQL Server 動態行轉列(參數化表名、分組列、行轉列字段、字段值)

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

  1. 本文所涉及的內容(Contents)
  2. 背景(Contexts)
  3. 實現代碼(SQL Codes)
    1. 方法一:使用拼接SQL,靜態列字段;
    2. 方法二:使用拼接SQL, 動態 列字段;
    3. 方法三:使用PIVOT關系運算符,靜態列字段;
    4. 方法四:使用PIVOT關系運算符, 動態 列字段;
    5. 擴展閱讀一:參數化表名、分組列、行轉列字段、字段值;
    6. 擴展閱讀二:在前面的基礎上加入條件過濾;
  4. 參考文獻(References)

二.背景(Contexts)

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

wps_clip_image-32543

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

wps_clip_image-17757

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

wps_clip_image-10798

(圖6)

四.參考文獻(References)

使用 PIVOT 和 UNPIVOT

SQL Server 動態行轉列(參數化表名、分組列、行轉列字段、字段值)


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

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

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。?!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 亚洲一区 在线播放 | 四虎www成人影院免费观看 | 天天综合久久 | 欧美日韩在线网站 | 免费香蕉成视频成人网 | 96影院| 久久精品国产99久久无毒不卡 | 黄页在线播放网址 | 国产视频在线观看福利 | 天天夜夜狠狠 | 国产精品免费福利 | 中文字幕影院 | 四虎影在线永久免费观看 | 国内精品福利 | 伊人情人综合 | 天天操人人| 成 人 黄 色 大 片 | 我想看一级毛片免费的 | 9久久免费国产精品特黄 | 久操视频在线免费观看 | 手机看片福利久久 | 欧美激情第一区 | www.四虎在线 | 国产一级大片免费看 | 视频一区二区国产 | 在线观看精品国语偷拍 | 国产一区不卡 | 精品久久久久久久久久香蕉 | 四虎影院在线看 | 日本久久中文字幕 | 四虎影院最新网站 | 亚洲品质自拍视频 | 亚洲一区二区福利视频 | 国产在线精品福利91香蕉 | 99国产精品久久久久久久成人热 | 亚洲精品国产啊女成拍色拍 | 亚洲a毛片| 成人一级 | 激情综合在线 | 亚洲无线码一区在线观看 | 亚洲乱强|