行转列脚本示例

状况1：

        select dev_no,
               dot_no,
               sum(decode(to_char(savetime, 'hh24'), '00', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '01', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '02', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '03', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '04', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '05', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '06', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '07', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '08', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '09', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '10', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '11', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '12', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '13', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '14', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '15', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '16', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '17', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '18', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '19', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '20', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '21', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '22', saveval, 0)),
               sum(decode(to_char(savetime, 'hh24'), '23', saveval, 0))
          from ycdata200807 partition(YCDATA200807_DAY8)
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmdd') = 20080708
           and to_char(savetime, 'mi') = '00'
         group by dev_no, dot_no;

 

状况二：

 

select (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080000),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080100),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080200),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080300),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080400),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080500),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080600),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080700),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080800),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807080900),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081000),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081100),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081200),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081300),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081400),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081500),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081600),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081700),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081800),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807081900),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807082000),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807082100),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807082200),
       (select saveval
          from ycdata200807
         where dev_no = 241
           and dot_no = 20
           and to_char(savetime, 'yyyymmddhh24mi') = 200807082300)
  from dual;

 

 

状况三：

select nvl(b.fac_name, '??'),
       nvl(c.devtype_name, '??'),
       nvl(d.dev_name, '??'),
       nvl(e.dot_name, '??'),
       sum(decode(stat_type, 201, saveval, 0)),
       sum(decode(stat_type, 202, saveval, 0)),
       sum(decode(stat_type, 203, saveval, 0)),
       to_date(sum(decode(stat_type,
                          201,
                          to_number(to_char(OCCURTIME, 'yyyymmddhh24miss')),
                          0)),
               'yyyy-mm-dd hh24:mi:ss'),
       to_date(sum(decode(stat_type,
                          202,
                          to_number(to_char(OCCURTIME, 'yyyymmddhh24miss')),
                          0)),
               'yyyy-mm-dd hh24:mi:ss'),
       sum(decode(stat_type, 211, saveval, 0)),
       sum(decode(stat_type, 213, saveval, 0)),
       sum(decode(stat_type, 210, saveval, 0)),
       sum(decode(stat_type, 212, saveval, 0))
  from ycdatamonth2008 a, facname b, devtype c, devname d, dotname e
 where to_char(a.savetime, 'yyyy-mm') = '2008-08'
   and a.fac_no = b.fac_no
   and a.devtype_no = c.devtype_no
   and a.dev_no = d.dev_no
   and a.dot_no = e.dot_no
 group by nvl(b.fac_name, '??'),
          nvl(c.devtype_name, '??'),
          nvl(d.dev_name, '??'),
          nvl(e.dot_name, '??')