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

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條評論
主站蜘蛛池模板: 国产第一区二区三区在线观看 | 成年女人aaaaa毛片 | 成人免费久久精品国产片久久影院 | 免费视频毛片 | 韩国高清乱理伦片中文 | 中文字幕综合 | 一本清高清dvd日本播放器 | 日本一级毛片免费播放 | 欧美最猛性xxxxx短视频 | 国产精品国产三级国产无毒 | 天天做日日做 | 国产午夜成人无码免费看 | 成人三级毛片 | 伊人狼人综合网 | 国产精品成人免费 | 99在线视频免费观看 | 黄色在线免费看 | 黄页网址大全免费观看美女 | 久久久国产精品网站 | 伊人网综合在线观看 | 亚洲小视频在线观看 | 99久久精品久久久久久婷婷 | 全部费免一级毛片不收费 | 国产成人免费a在线视频色戒 | 日本老年人精品久久中文字幕 | 激情综合网婷婷 | 7777精品伊人久久久大香线蕉 | 久久精品94精品久久精品 | 亚洲欧洲精品在线 | 亚洲视频 欧美视频 | a级亚洲片精品久久久久久久 | 久久首页| 天天做天天爱夜夜大爽完整 | 999热在线精品观看全部 | 久久精品成人免费网站 | 国产精品在线播放 | xxx大片免费视频 | 亚洲一级片在线观看 | 久久ri精品高清一区二区三区 | 99久久免费精品国产免费 | 久久精品国产亚洲黑森林 |