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

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影院在线午夜 | 波多野结衣中文字幕久久 | 久久亚洲精品专区蓝色区 | 精品中文字幕久久久久久 | 日本另类αv欧美另类aⅴ | 四虎永久免费在线 | 高清欧美日本视频免费观看 | 久久色吧| 亚欧成人在线 | 神马我不卡在线观看 | 日韩高清一区二区三区不卡 | h片免费看 | 亚洲一级黄色 | 国产福利视频奶水在线 | 亚洲最大色视频 | 久久爱噜噜噜噜久久久网 | 天天狠天天操 | 亚洲国产天堂在线mv网站 | 国产美女久久 | 奇米青青草| 中文字幕在线视频精品 | 精品国产一区二区三区19 | 亚欧洲精品bb | 亚洲精品123区在线观看 | 久久都是精品 | 国产高清在线精品一区免费97 | 久久不雅视频 | 色八戒国产一区二区三区四区 | 国产伦精品一区二区三区免费迷 | 性影院| 国产精品va | 国产香蕉久久精品综合网 | 性一交一乱一视频免费看 | 日本高清免费不卡毛片 | 亚洲欧洲免费 | 亚洲精品久久久久久久久久ty | 欧美成人鲁丝片在线观看 | 99涩涩| 久久精品国产一区二区三区 |