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

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條評論
主站蜘蛛池模板: 亚洲国产精品乱码在线观看97 | 一级一级毛片看看 | 香香在线观看视频 | 精品久久久久久中文字幕2017 | 久久久久国产视频 | 久久国产加勒比精品无码 | 日本一区二区三区欧美在线观看 | 日韩高清成人 | 黄wwwwww| 日韩视频网址 | 九九久久精品这里久久网 | 天天干天天操天天添 | 四虎影视在线播放 | 国产高清视频免费 | 夜夜操美女 | 国产大片免费观看中文字幕 | 日韩不卡视频在线 | 精品欧美在线 | 九九久久国产精品免费热6 九九久久精品 | 免费看欧美一级片 | 国产成年人视频 | 香蕉久久一区二区三区 | 日产国产欧美视频一区精品 | 国内精品久久国产大陆 | 国产成人综合视频 | 97se狠狠狠狠狼亚洲综合网 | 日本一级α一片免费视频 | 中文字幕不卡在线观看 | 天天做夜夜做久久做狠狠 | 四虎在线精品 | 色涩播| 911免费视频 | 国产aa免费视频 | 尤物精品视频在线观看 | 成人毛片18女人毛片免费96 | 精品一久久香蕉国产线看观 | 777奇米影视色888成人 | 久久精品国产线看观看亚洲 | 色视在线 | 91精品国产乱码在线观看 | 黄色在线观看视频免费 |