横表变纵表

横表变纵表列子1

横表:



纵表：




中介表:

with tb1 as
 (select '张三' fname,
         'A张三' f1,
         'B张三' f2,
         'C张三' f3,
         'D张三' f4
    from dual
  union all
  select '王五' fname,
         'A王五' f1,
         'B王五' f2,
         'C王五' f3,
         'D王五' f4
    from dual
  union all
  select '李四' fname,
         'A李四' f1,
         'B李四' f2,
         'C李四' f3,
         'D李四' f4
    from dual),

tb2 as
 (select 'F1' f from dual
      union all
  select 'F2' f from dual 
      union all
  select 'F3' f from dual  
     union all
  select 'F4' f from dual  
 )

select tb1.fname,
       tb2.f,
       decode(tb2.f , 'F1', tb1.f1, 'F2', f2, 'F3', f3, 'F4', f4) f_val
  from tb1, tb2
 order by tb1.fname,tb2.f

横表变纵表列子2：

with tb1 as
 (select '张三' fname,
         '201007' month_id,
         'A张三' f1,
         'B张三' f2,
         'C张三' f3,
         'D张三' f4
    from dual
  union all
  select '王五' fname,
         '201007' month_id,
         'A王五' f1,
         'B王五' f2,
         'C王五' f3,
         'D王五' f4
    from dual
  union all
  select '李四' fname,
         '201007' month_id,
         'A李四' f1,
         'B李四' f2,
         'C李四' f3,
         'D李四' f4
    from dual),
tb2 as
 (select rownum day_id
    from dual
  connect by rownum <=
             to_number(to_char(last_day(to_date('201007', 'YYYYMM')), 'dd')))

select tb1.fname,
       tb1.month_id,
       tb2.day_id,
       decode(mod(tb2.day_id, 4) + 1, 1, tb1.f1, 2, f2, 3, f3, 4, f4) f_val
  from tb1, tb2
 group by tb1.fname,
          tb1.month_id,
          tb2.day_id,
          decode(mod(tb2.day_id, 4) + 1, 1, tb1.f1, 2, f2, 3, f3, 4, f4)
 order by tb1.fname