Converting Rows into Columns without affecting the data in the table.

[truva]

Hi,
I need a query which would convert Rows into Columns without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it.

I have a table which is in the below format:

LOGTYPE CLLI SWREL RPTDATE CNAME CVALUE
avl-stplan tahlt01 EAGLE537 08/11/2007 TCPRSTSENT 12
avl-stplan tahlt01 EAGLE537 08/11/2007 IPHDRERR 88
avl-stplan tahlt01 EAGLE537 08/11/2007 IPADDRERR 65
avl-stplan tahlt01 EAGLE537 08/11/2007 IPPROTERR 34
avl-stplan tahlt01 EAGLE537 08/11/2007 STATUS N
avl-stplan tahlt01 EAGLE537 08/11/2007 TYPE LIM
avl-stplan tahlt01 EAGLE537 08/11/2007 LOC 1216
avl-stplan tahlt02 EAGLE537 08/11/2007 TCPRSTSENT 40
avl-stplan tahlt02 EAGLE537 08/11/2007 IPHDRERR 22
avl-stplan tahlt02 EAGLE537 08/11/2007 IPADDRERR 60
avl-stplan tahlt02 EAGLE537 08/11/2007 IPPROTERR 28
avl-stplan tahlt02 EAGLE537 08/11/2007 STATUS K
avl-stplan tahlt02 EAGLE537 08/11/2007 TYPE LIM
avl-stplan tahlt02 EAGLE537 08/11/2007 LOC 1209



I need a Output which has the following format:

LOGTYPE CLLI SWREL RPTDATE TCPRSTSENT IPHDRERR IPADDRERR IPPROTERR STATUS TYPE LOC
avl-stplan tahlt01 EAGLE537 08/11/2007 12 88 65 34 N LIM 1216
avl-stplan tahlt02 EAGLE537 08/11/2007 40 22 60 28 K LIM 1209

NOTE: I just gave above data as a sample. Actually the number of CNAME rows is more than 30 per (LOGTYPE and CLLI and RPTDATE). For this reason, (if available) I do not want write every CNAME in solution sql.

Please help with some solution.

Thanks,
Recep

[chritzuk]

Here's how you can do a pivot query in Oracle:

chris@XE> drop table t;
 
Table dropped.
 
Elapsed: 00:00:00.04
chris@XE>
chris@XE> create table t (
  2  LOGTYPE  varchar2(20),
  3  CLLI     varchar2(20),
  4  SWREL    varchar2(20),
  5  RPTDATE  date,
  6  CNAME    varchar2(20),
  7  CVALUE   varchar2(20)
  8  );
 
Table created.
 
Elapsed: 00:00:00.01
chris@XE>
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TCPRSTSENT' ,'12');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPHDRERR'   ,'88');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPADDRERR'  ,'65');
 
1 row created.
 
Elapsed: 00:00:00.00
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPPROTERR'  ,'34');
 
1 row created.
 
Elapsed: 00:00:00.00
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'STATUS'     ,'N');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TYPE'       ,'LIM');
 
1 row created.
 
Elapsed: 00:00:00.00
chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'LOC'        ,'1216');
 
1 row created.
 
Elapsed: 00:00:00.00
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TCPRSTSENT' ,'40');
 
1 row created.
 
Elapsed: 00:00:00.00
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPHDRERR'   ,'22');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPADDRERR'  ,'60');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPPROTERR'  ,'28');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'STATUS'     ,'K');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TYPE'       ,'LIM');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'LOC'        ,'1209');
 
1 row created.
 
Elapsed: 00:00:00.01
chris@XE>
chris@XE> commit;
 
Commit complete.
 
Elapsed: 00:00:00.01
chris@XE>
chris@XE> select * from t
  2
chris@XE> SELECT   logtype,
  2           clli,
  3           swrel,
  4           rptdate,
  5           MAX (DECODE (cname,'TCPRSTSENT', cvalue)) tcprstsent,
  6           MAX (DECODE (cname,'IPHDRERR', cvalue)) IPHDRERR,
  7           MAX (DECODE (cname,'IPADDRERR', cvalue)) IPADDRERR,
  8           MAX (DECODE (cname,'IPPROTERR', cvalue)) IPPROTERR,
  9           MAX (DECODE (cname,'STATUS', cvalue)) STATUS,
 10           MAX (DECODE (cname,'TYPE', cvalue)) TYPE,
 11           MAX (DECODE (cname,'LOC', cvalue)) LOC
 12  FROM     t
 13  GROUP BY logtype,
 14           clli,
 15           swrel,
 16           rptdate;
 
LOGTYPE     CLLI     SWREL     RPTDATE      TCPRSTSENT  IPHDRERR  IPADDRERR  IPPROTERR  STATUS  TYPE  LOC
----------- -------- --------- ------------ ----------- --------- ---------- ---------- ------- ----- -----
avl-stplan  tahlt02  EAGLE537  08-NOV-2007  40          22        60         28         K       LIM   1209
avl-stplan  tahlt01  EAGLE537  08-NOV-2007  12          88        65         34         N       LIM   1216
 
Elapsed: 00:00:00.03
chris@XE>
 

[debasisdas]

Please find a related discussion here and few others in the How To section of Oracle.