文章出處

sql行轉列和列轉行。最近建立數據立方體需要將表的數據結構進行轉換，進行列轉行，覺得用途還很大，所以就整理一下，當做自己的筆記拉。

1、列轉行（主要）

表weatherdata結構：

create table WEATHERDATA(  路徑   VARCHAR2(100),  日期   VARCHAR2(50),  時間   VARCHAR2(30),  溫度   VARCHAR2(50),  P0   VARCHAR2(50),  P    VARCHAR2(50),  PA   VARCHAR2(50),  濕度   VARCHAR2(10),  風向   VARCHAR2(100),  風速   VARCHAR2(50),  FF10 VARCHAR2(100),  FF3  VARCHAR2(100),  總云量  VARCHAR2(100),  WW   VARCHAR2(200),  W1   VARCHAR2(100),  W2   VARCHAR2(100),  TN   VARCHAR2(100),  TX   VARCHAR2(100),  CL   VARCHAR2(200),  NH   VARCHAR2(100),  H    VARCHAR2(100),  CM   VARCHAR2(200),  CH   VARCHAR2(200),  VV   VARCHAR2(100),  TD   VARCHAR2(100),  RRR  VARCHAR2(100),  TR   VARCHAR2(100),  E    VARCHAR2(100),  TG   VARCHAR2(100),  E1   VARCHAR2(100),  SSS  VARCHAR2(100))

數據示例如下：

\

需要裝換的效果：

\

轉換成五列，原來表中溫度，P0，P，PA，濕度，風向，風速等對應INDI的屬性id值，VALUE為每個屬性對應值。

sql代碼：

select key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '24' indi,溫度 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '25' indi,P0 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '26' indi,P as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '27' indi,PA as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '28' indi,濕度 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '29' indi,風向 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '30' indi,風速 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '31' indi,FF10 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '32' indi,FF3 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '33' indi,總云量 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '34' indi,WW as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '35' indi,W1 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '36' indi,W2 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '37' indi,TN as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '38' indi,TX as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '39' indi,CL as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '40' indi,NH as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '41' indi,H as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '42' indi,CM as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '43' indi,CH as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '44' indi,VV as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '45' indi,TD as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '46' indi,RRR as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '47' indi,TR as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '48' indi,E as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '49' indi,TG as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '50' indi,E1 as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑unionselect key2 as 路徑,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 時間, '51' indi,SSS as value from weatherdata a,T72090608 b,area_id c where a.時間 = b.name and a.路徑 = c.路徑order by 路徑,日期,時間,indi

按照自己的需求可以參考。

2、行轉列

行轉列效果相反，主要原理是利用decode函數(將查詢結果翻譯成其他值)、聚集函數（sum,max,min），結合group by 分組實現。這邊簡單說下：

sql代碼如下：

select t.area,t.date1,t.time,      sum(decode(t.indi, '24', value,null)) as 溫度,      sum(decode(t.indi, '25', value,null)) as P0,      sum(decode(t.indi, '26', value,null)) as P,      sum(decode(t.indi, '27', value,null)) as PA,      max(decode(t.indi, '28', value,null)) as 濕度,      max(decode(t.indi, '29', value,null)) as 風向,      sum(decode(t.indi, '30', value,null)) as 風速,      sum(decode(t.indi, '31', value,null)) as FF10,      sum(decode(t.indi, '32', value,null)) as FF3,      sum(decode(t.indi, '33', value,null)) as 總云量,      sum(decode(t.indi, '34', value,null)) as WW,      sum(decode(t.indi, '35', value,null)) as W1,      sum(decode(t.indi, '36', value,null)) as W2,      sum(decode(t.indi, '37', value,null)) as TN,      sum(decode(t.indi, '38', value,null)) as TX,      sum(decode(t.indi, '39', value,null)) as CL,      sum(decode(t.indi, '40', value,null)) as NH,      sum(decode(t.indi, '41', value,null)) as H,      sum(decode(t.indi, '42', value,null)) as CM,      sum(decode(t.indi, '43', value,null)) as CH,      sum(decode(t.indi, '44', value,null)) as VV,      sum(decode(t.indi, '45', value,null)) as TD,      sum(decode(t.indi, '46', value,null)) as RRR,      sum(decode(t.indi, '47', value,null)) as TR,      sum(decode(t.indi, '48', value,null)) as E,      sum(decode(t.indi, '49', value,null)) as TG,      sum(decode(t.indi, '50', value,null)) as E1,      sum(decode(t.indi, '51', value,null)) as SSS    from WEATHER1 t    group by t.area,t.date1,t.time

decode函數代表含義：

if （t.indi==24） then　　　　 return(value)//溫度對應值 else return(null)

注意：行轉列時，聚集函數的使用，一般非數字類型的字段不能用sum，可用max或min代替。
看文倉www.kanwencang.com網友整理上傳,為您提供最全的知識大全,期待您的分享，轉載請注明出處。
歡迎轉載：http://www.kanwencang.com/bangong/20170117/88921.html

文章列表


不含病毒。www.avast.com
arrow
arrow
    全站熱搜
    創作者介紹
    創作者 大師兄 的頭像
    大師兄

    IT工程師數位筆記本

    大師兄 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄