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

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看片 | 亚洲精品老司机综合影院 | 5g天天爽影院| 久久99精品国产麻豆 | 不卡精品国产_亚洲人成在线 | 久久2| 视频一区国产精戏刘婷 | 香蕉视频一级片 | 四房激情网 | 国产一在线精品一区在线观看 | 国产精品手机网站 | 日韩欧美aa级草草免费视频 | 99热这里只有精品一区二 | 国产精品久久久久免费视频 | 天天艹在线 | 四虎影院新网址 | 欧美japanese孕交 | 亚洲精品久久久久久久久久久网站 | 久久艹在线观看 | 九九精品视频在线 | 91精品国产9l久久久久 | 色片网 | 国产三级日产三级日本三级 | 久久爱伊人一区二区三区小说 | 一区二区三区免费视频 www | 人成午夜欧美大片免费视频 | 伊人首页 | 天天做天天爱夜夜爽毛片毛片 | 久久久久国产 | 天天插天天操天天干 | 国产精品久久久久久久小唯西川 | 国产日韩高清一区二区三区 | 久色精品| 午夜私人影院在线观看 | 成人毛片免费免费 | 国产大片免费观看中文字幕 | 亚洲精品影院一区二区 | 国产福利区一区二在线观看 |