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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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.路径
union
select 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代替。