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

03. 行列轉換寫法小結

系統 1952 0
原文: 03. 行列轉換寫法小結

行列轉換,通常有2種方法,一是CASE WHEN/UNION;一是PIVOT/UNPIVOT。對于行值或列數不固定的情況,需要用動態SQL。


一. 行轉列

      
        --
      
      
        drop table RowToCol
      
      
        create
      
      
        table
      
      
         RowToCol

(

ID 
      
      
        int
      
      
        ,

Code 
      
      
        varchar
      
      (
      
        10
      
      
        ),

Value 
      
      
        int
      
      
        

)


      
      
        Go
      
      
        insert
      
      
         RowToCol 


      
      
        select
      
      
        1
      
      ,
      
        '
      
      
        Item1
      
      
        '
      
      ,
      
        1000
      
      
        union
      
      
        all
      
      
        select
      
      
        1
      
      ,
      
        '
      
      
        Item2
      
      
        '
      
      ,
      
        1000
      
      
        union
      
      
        all
      
      
        select
      
      
        1
      
      ,
      
        '
      
      
        Item3
      
      
        '
      
      ,
      
        500
      
      
        union
      
      
        all
      
      
        select
      
      
        2
      
      ,
      
        '
      
      
        Item1
      
      
        '
      
      ,
      
        2000
      
      
        union
      
      
        all
      
      
        select
      
      
        2
      
      ,
      
        '
      
      
        Item2
      
      
        '
      
      ,
      
        0
      
      
        union
      
      
        all
      
      
        select
      
      
        3
      
      ,
      
        '
      
      
        Item1
      
      
        '
      
      ,
      
        1000
      
      
        union
      
      
        all
      
      
        select
      
      
        3
      
      ,
      
        '
      
      
        Item3
      
      
        '
      
      ,
      
        500
      
      
        GO
      
      
        select
      
      
        *
      
      
        from
      
       RowToCol
    

? 要得到這樣的結果:

ID Item1 Item2 Item3
1 1000 1000 500
2 2000 0 0
3 1000 0 500


1. CASE WHEN
在SQL Server 2000時,常用的寫法,沿用至今。
(1) 靜態

      
        select
      
      
         ID,


      
      
        sum
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item1
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item1,


      
      
        sum
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item2
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item2,


      
      
        sum
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item3
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item3


      
      
        from
      
       RowToCol 
      
        group
      
      
        by
      
      
         ID


      
      
        --
      
      
        或者用max也行
      
      
        select
      
      
         ID,


      
      
        max
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item1
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item1,


      
      
        max
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item2
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item2,


      
      
        max
      
      (
      
        case
      
       Code 
      
        when
      
      
        '
      
      
        Item3
      
      
        '
      
      
        then
      
       Value 
      
        else
      
      
        0
      
      
        end
      
      ) 
      
        as
      
      
         Item3


      
      
        from
      
       RowToCol 
      
        group
      
      
        by
      
       ID
    

?(2) 動態

在不確定有多少行需要轉為列時,先distinct出待轉列的值,再拼出包含若干個CASE的SQL語句,然后運行。

      
        declare
      
      
        @sql
      
      
        varchar
      
      (
      
        8000
      
      
        )


      
      
        set
      
      
        @sql
      
      
        =
      
      
        '
      
      
        select ID 
      
      
        '
      
      
        select
      
      
        @sql
      
      
        =
      
      
        @sql
      
      
        +
      
      
        '
      
      
         , max(case Code when 
      
      
        '''
      
      
        +
      
       Code 
      
        +
      
      
        '''
      
      
         then Value else 0 end) [
      
      
        '
      
      
        +
      
       Code 
      
        +
      
      
        '
      
      
        ]
      
      
        '
      
      
        from
      
       (
      
        select
      
      
        distinct
      
       Code 
      
        from
      
       RowToCol) 
      
        as
      
      
         a


      
      
        set
      
      
        @sql
      
      
        =
      
      
        @sql
      
      
        +
      
      
        '
      
      
         from RowToCol group by ID
      
      
        '
      
      
        --
      
      
        print @sql
      
      
        exec
      
      (
      
        @sql
      
      )
    

?

2. PIVOT
PIVOT是SQL Server 2005的新語法,Oracle在11g中也推出了這個語法。
(1) 靜態

      
        select
      
      
        *
      
      
        from
      
       (
      
        select
      
      
        *
      
      
        from
      
      
         RowToCol) a 

pivot (
      
      
        max
      
      (value) 
      
        for
      
       Code 
      
        in
      
       (
      
        [
      
      
        Item1
      
      
        ]
      
      ,
      
        [
      
      
        Item2
      
      
        ]
      
      ,
      
        [
      
      
        Item3
      
      
        ]
      
      )) b
    

?(2) 動態

用PIVOT拼寫動態SQL時就簡單了,只要把后面的列清單整理出來就可以了。

      
        declare
      
      
        @sql
      
      
        varchar
      
      (
      
        8000
      
      
        )


      
      
        select
      
      
        @sql
      
      
        =
      
      
        isnull
      
      (
      
        @sql
      
      
        +
      
      
        '
      
      
        ],[
      
      
        '
      
       , 
      
        ''
      
      ) 
      
        +
      
       Code 
      
        from
      
       RowToCol 
      
        group
      
      
        by
      
      
         Code


      
      
        set
      
      
        @sql
      
      
        =
      
      
        '
      
      
        [
      
      
        '
      
      
        +
      
      
        @sql
      
      
        +
      
      
        '
      
      
        ]
      
      
        '
      
      
        --
      
      
        print @sql
      
      
        exec
      
       (
      
        '
      
      
        select * from (select * from RowToCol) a pivot (max(value) for Code in (
      
      
        '
      
      
        +
      
      
        @sql
      
      
        +
      
      
        '
      
      
        )) b
      
      
        '
      
      )
    

?

二. 列轉行

      
        --
      
      
        drop table ColToRow
      
      
        create
      
      
        table
      
      
         ColToRow

(

ID 
      
      
        int
      
      
        ,

Item1 
      
      
        int
      
      
        ,

Item2 
      
      
        int
      
      
        ,

Item3 
      
      
        int
      
      
        

)


      
      
        GO
      
      
        insert
      
      
        into
      
      
         ColToRow


      
      
        select
      
      
        '
      
      
        1
      
      
        '
      
      ,
      
        1000
      
      ,
      
        1000
      
      ,
      
        500
      
      
        union
      
      
        all
      
      
        select
      
      
        '
      
      
        2
      
      
        '
      
      ,
      
        2000
      
      ,
      
        0
      
      ,
      
        0
      
      
        union
      
      
        all
      
      
        select
      
      
        '
      
      
        3
      
      
        '
      
      ,
      
        1000
      
      ,
      
        0
      
      ,
      
        500
      
      
        GO
      
      
        select
      
      
        *
      
      
        from
      
       ColToRow
    

? 要得到這樣的結果:

ID Code Value
1 Item1 1000
1 Item2 1000
1 Item3 500
2 Item1 2000
2 Item2 0
2 Item3 0
3 Item1 1000
3 Item2 0
3 Item3 500

1. UNION
在SQL Server 2000時,常用的寫法,沿用至今。
(1) 靜態

      
        select
      
       ID,Code
      
        =
      
      
        '
      
      
        Item1
      
      
        '
      
      ,Value
      
        =
      
      Item1 
      
        from
      
      
         ColToRow


      
      
        union
      
      
        all
      
      
        select
      
       ID,Code
      
        =
      
      
        '
      
      
        Item2
      
      
        '
      
      ,Value
      
        =
      
      Item2 
      
        from
      
      
         ColToRow


      
      
        union
      
      
        all
      
      
        select
      
       ID,Code
      
        =
      
      
        '
      
      
        Item3
      
      
        '
      
      ,Value
      
        =
      
      Item3 
      
        from
      
      
         ColToRow


      
      
        order
      
      
        by
      
       ID
    

? SQL Server對于多個UNION的排序,只要在最后加ORDER BY就可以了。

(2) 動態
在不確定有多少列需要轉為行時,先借助系統表syscolumns找出待轉行的列,再拼出包含若干個UNION語句,然后運行。

      
        declare
      
      
        @sql
      
      
        varchar
      
      (
      
        8000
      
      
        )


      
      
        select
      
      
        @sql
      
      
        =
      
      
        isnull
      
      (
      
        @sql
      
      
        +
      
      
        '
      
      
         union all 
      
      
        '
      
       , 
      
        ''
      
       ) 
      
        +
      
      
        '
      
      
         select ID , [Code] = 
      
      
        '
      
      
        +
      
      
        quotename
      
      (Name , 
      
        ''''
      
      ) 
      
        +
      
      
        '
      
      
         , [Value] = 
      
      
        '
      
      
        +
      
      
        quotename
      
      (Name) 
      
        +
      
      
        '
      
      
         from ColToRow
      
      
        '
      
      
        from
      
      
         syscolumns 


      
      
        where
      
       name 
      
        <>
      
       N
      
        '
      
      
        ID
      
      
        '
      
      
        and
      
       ID 
      
        =
      
      
        object_id
      
      (
      
        '
      
      
        ColToRow
      
      
        '
      
      
        )


      
      
        order
      
      
        by
      
       colid 
      
        asc
      
      
        --
      
      
        print @sql
      
      
        exec
      
      (
      
        @sql
      
      
        +
      
      
        '
      
      
         order by ID 
      
      
        '
      
      )
    

?

2. UNPIVOT
UNPIVOT是SQL Server 2005的新語法,Oracle在11g中也推出了這個語法。
(1) 靜態

      
        select
      
       ID , Code , Value 
      
        from
      
      
         ColToRow 

unpivot (Value 
      
      
        for
      
       Code 
      
        in
      
      (
      
        [
      
      
        Item1
      
      
        ]
      
       , 
      
        [
      
      
        Item2
      
      
        ]
      
       , 
      
        [
      
      
        Item3
      
      
        ]
      
      )) t
    

?(2) 動態

      
        declare
      
      
        @sql
      
      
        varchar
      
      (
      
        8000
      
      
        )


      
      
        select
      
      
        @sql
      
      
        =
      
      
        isnull
      
      (
      
        @sql
      
      
        +
      
      
        '
      
      
        ],[
      
      
        '
      
       , 
      
        ''
      
      ) 
      
        +
      
       name 
      
        from
      
      
         syscolumns 


      
      
        where
      
       name 
      
        <>
      
       N
      
        '
      
      
        ID
      
      
        '
      
      
        and
      
       ID 
      
        =
      
      
        object_id
      
      (
      
        '
      
      
        ColToRow
      
      
        '
      
      
        )


      
      
        set
      
      
        @sql
      
      
        =
      
      
        '
      
      
        [
      
      
        '
      
      
        +
      
      
        @sql
      
      
        +
      
      
        '
      
      
        ]
      
      
        '
      
      
        --
      
      
        print @sql
      
      
        exec
      
      (
      
        '
      
      
        select ID , Code , Value from ColToRow unpivot (Value for Code in(
      
      
        '
      
      
        +
      
      
        @sql
      
      
        +
      
      
        '
      
      
        )) t
      
      
        '
      
      )
    

?

?

03. 行列轉換寫法小結


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

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

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

【本文對您有幫助就好】

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

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 色综合久久久久久久久久久 | 国产精彩视频 | 激情开心婷婷 | 美女毛片 | 亚洲国产福利精品一区二区 | 国产欧美日韩高清专区手机版 | 亚洲精品成人一区二区www | 亚洲精品一区二区三区网址 | 精品综合在线 | 亚洲va在线va天堂成人 | 国产成人综合一区人人 | 99九九精品| 久久精品国产国产精品四凭 | 天天干天天操天天射 | 亚洲精品色综合久久久 | 日本欧美另类 | 国产一级免费在线观看 | 成年人黄视频大全 | 天天爽天天干天天操 | 欧美不卡精品中文字幕日韩 | 97欧美| 九九欧美 | 久久综合狠狠综合久久 | 国产精品公开免费视频 | 99视频精品全国免费 | 四虎免费紧急入口观看 | 澳门四虎影院 | 91国内精品视频 | 国产欧美日韩看片片在线人成 | 国产成人精品影院狼色在线 | 午夜dj影院在线视频观看完整 | 国产精品亚洲精品 | 中文字幕一区二区三区精彩视频 | 国产精品中文字幕在线观看 | 狠狠色很很在鲁视频 | 综合久久精品 | 久青草国产观看在线视频 | 日本精品视频在线观看 | 亚洲精品乱码久久久久久蜜桃欧美 | 日韩爱爱视频 | 久久高清一区二区三区 |