1,169   Oracle

数据表格的字段设计成日期,如下图

QQ图片20151231102748

字段名 one,two,three·····对应日期 1号,2号,3号 ····
不要喷我,数据库不是我设计的

但是需求数据显示却是要这样:

QQ图片20151231102940

换句话说,要将原始表的列数据,变成需求显示的行数据,这涉及到行列转换

网上有介绍方法,包含了大部分行列、字符之间的转换:
http://www.itpub.net/thread-1017026-1-1.html

参考其中的方法,综合考虑,我先把列转换成字符串,然后再讲字符串转换成行,这是最简单易更新的

1,将列转换成字符串,这个比较简单,直接用||拼接

           
 select
            t.tj_mon, 
            t.one||','||t.two||','||t.three||','||t.four||','||t.five||','||t.six||','||t.seven||','||t.eight||','||t.nine||','||t.ten||','||t.eleven||','||t.twelve||','||t.thirteen||','||t.fourteen||','||t.fifteen||','||t.sixteen||','||t.seventeen||','||t.eighteen||','||t.nineteen||','||t.twenty||','||t.twenty_one||','||t.twenty_two||','||t.twenty_three||','||t.twenty_four||','||t.twenty_five||','||t.twenty_six||','||t.twenty_seven||','||t.twenty_eight||','||t.twenty_nine||','||t.thirty||','||t.thirty_one as str
            from wcs_order_sq_detail t 
            where t.tj_type = 'xk'               
            and t.city_code = 'sum'                
            and t.tj_mon in( 
              to_char(trunc(sysdate,'MM'),'yyyymm'),
              to_char(add_months(sysdate,-1),'yyyymm'),
              to_char(add_months(sysdate,-2),'yyyymm')
              )

效果图:

QQ图片20151231103812

 

2,分割字符串成行,同时分出月份和日期的字段

      


      SELECT
      tj_mon tj_mon,
      level mon_day,
      rtrim(regexp_substr(str || ',', '.*?' || ',', 1, level), ',')  day_data
      FROM (
      select
        t.tj_mon, 
        t.one||','||t.two||','||t.three||','||t.four||','||t.five||','||t.six||','||t.seven||','||t.eight||','||t.nine||','||t.ten||','||t.eleven||','||t.twelve||','||t.thirteen||','||t.fourteen||','||t.fifteen||','||t.sixteen||','||t.seventeen||','||t.eighteen||','||t.nineteen||','||t.twenty||','||t.twenty_one||','||t.twenty_two||','||t.twenty_three||','||t.twenty_four||','||t.twenty_five||','||t.twenty_six||','||t.twenty_seven||','||t.twenty_eight||','||t.twenty_nine||','||t.thirty||','||t.thirty_one as str
      from wcs_order_sq_detail t 
        where t.tj_type = 'xk'               
        and t.city_code = 'sum'                
        and t.tj_mon in( 
        to_char(trunc(sysdate,'MM'),'yyyymm'),
        
        to_char(add_months(sysdate,-1),'yyyymm'),
        
        to_char(add_months(sysdate,-2),'yyyymm')
        
        )
      )
      CONNECT BY tj_mon = PRIOR tj_mon
      AND PRIOR dbms_random.VALUE IS NOT NULL 
      AND LEVEL <= length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))

效果图:

QQ图片20151231104323

 

 

3,按日期分类,同时分出月份数据的字段

   

    select 
      mon_day mon_day,  
      max(decode(tj_mon,to_char(trunc(sysdate,'MM'),'yyyymm'),day_data,null)) this_mon,
      max(decode(tj_mon,to_char(add_months(sysdate,-1),'yyyymm'),day_data,null)) last_mon,
      max(decode(tj_mon,to_char(add_months(sysdate,-2),'yyyymm'),day_data,null)) before_mon
    from(
      
      SELECT
      tj_mon tj_mon,
      level mon_day,
      rtrim(regexp_substr(str || ',', '.*?' || ',', 1, level), ',')  day_data
      FROM (
      select
        t.tj_mon, 
        t.one||','||t.two||','||t.three||','||t.four||','||t.five||','||t.six||','||t.seven||','||t.eight||','||t.nine||','||t.ten||','||t.eleven||','||t.twelve||','||t.thirteen||','||t.fourteen||','||t.fifteen||','||t.sixteen||','||t.seventeen||','||t.eighteen||','||t.nineteen||','||t.twenty||','||t.twenty_one||','||t.twenty_two||','||t.twenty_three||','||t.twenty_four||','||t.twenty_five||','||t.twenty_six||','||t.twenty_seven||','||t.twenty_eight||','||t.twenty_nine||','||t.thirty||','||t.thirty_one as str
      from wcs_order_sq_detail t 
        where t.tj_type = 'xk'               
        and t.city_code = 'sum'                
        and t.tj_mon in( 
        to_char(trunc(sysdate,'MM'),'yyyymm'),
        
        to_char(add_months(sysdate,-1),'yyyymm'),
        
        to_char(add_months(sysdate,-2),'yyyymm')
        
        )
      )
      CONNECT BY tj_mon = PRIOR tj_mon
      AND PRIOR dbms_random.VALUE IS NOT NULL 
      AND LEVEL <= length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
            
    )group by mon_day order by mon_day asc


效果图:

QQ图片20151231104822

 

 

4,至此,已经完成了列转行,下面是完整的 SQL

            
(
  select 
  mon_day||'号' 日期,
  this_mon 当月,
  last_mon 上月,
  before_mon 前月
  
  from(

  
      select 
      mon_day mon_day,  
      max(decode(tj_mon,to_char(trunc(sysdate,'MM'),'yyyymm'),day_data,null)) this_mon,
      max(decode(tj_mon,to_char(add_months(sysdate,-1),'yyyymm'),day_data,null)) last_mon,
      max(decode(tj_mon,to_char(add_months(sysdate,-2),'yyyymm'),day_data,null)) before_mon
    from(
      
      SELECT
      tj_mon tj_mon,
      level mon_day,
      rtrim(regexp_substr(str || ',', '.*?' || ',', 1, level), ',')  day_data
      FROM (
      select
        t.tj_mon, 
        t.one||','||t.two||','||t.three||','||t.four||','||t.five||','||t.six||','||t.seven||','||t.eight||','||t.nine||','||t.ten||','||t.eleven||','||t.twelve||','||t.thirteen||','||t.fourteen||','||t.fifteen||','||t.sixteen||','||t.seventeen||','||t.eighteen||','||t.nineteen||','||t.twenty||','||t.twenty_one||','||t.twenty_two||','||t.twenty_three||','||t.twenty_four||','||t.twenty_five||','||t.twenty_six||','||t.twenty_seven||','||t.twenty_eight||','||t.twenty_nine||','||t.thirty||','||t.thirty_one as str
      from wcs_order_sq_detail t 
        where t.tj_type = 'xk'               
        and t.city_code = 'sum'                
        and t.tj_mon in( 
        to_char(trunc(sysdate,'MM'),'yyyymm'),
        
        to_char(add_months(sysdate,-1),'yyyymm'),
        
        to_char(add_months(sysdate,-2),'yyyymm')
        
        )
      )
      CONNECT BY tj_mon = PRIOR tj_mon
      AND PRIOR dbms_random.VALUE IS NOT NULL 
      AND LEVEL <= length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
            
    )group by mon_day order by mon_day asc
  
  )

)





Leave a Reply

Your email address will not be published. Required fields are marked *