By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,068 Members | 618 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,068 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Dec 24 '07 #1
Share this Question
Share on Google+
2 Replies

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

Expand|Select|Wrap|Line Numbers
  1. chris@XE> drop table t;
  2.  
  3. Table dropped.
  4.  
  5. Elapsed: 00:00:00.04
  6. chris@XE>
  7. chris@XE> create table t (
  8.   2  LOGTYPE  varchar2(20),
  9.   3  CLLI     varchar2(20),
  10.   4  SWREL    varchar2(20),
  11.   5  RPTDATE  date,
  12.   6  CNAME    varchar2(20),
  13.   7  CVALUE   varchar2(20)
  14.   8  );
  15.  
  16. Table created.
  17.  
  18. Elapsed: 00:00:00.01
  19. chris@XE>
  20. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TCPRSTSENT' ,'12');
  21.  
  22. 1 row created.
  23.  
  24. Elapsed: 00:00:00.01
  25. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPHDRERR'   ,'88');
  26.  
  27. 1 row created.
  28.  
  29. Elapsed: 00:00:00.01
  30. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPADDRERR'  ,'65');
  31.  
  32. 1 row created.
  33.  
  34. Elapsed: 00:00:00.00
  35. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPPROTERR'  ,'34');
  36.  
  37. 1 row created.
  38.  
  39. Elapsed: 00:00:00.00
  40. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'STATUS'     ,'N');
  41.  
  42. 1 row created.
  43.  
  44. Elapsed: 00:00:00.01
  45. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TYPE'       ,'LIM');
  46.  
  47. 1 row created.
  48.  
  49. Elapsed: 00:00:00.00
  50. chris@XE> insert into t values('avl-stplan', 'tahlt01', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'LOC'        ,'1216');
  51.  
  52. 1 row created.
  53.  
  54. Elapsed: 00:00:00.00
  55. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TCPRSTSENT' ,'40');
  56.  
  57. 1 row created.
  58.  
  59. Elapsed: 00:00:00.00
  60. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPHDRERR'   ,'22');
  61.  
  62. 1 row created.
  63.  
  64. Elapsed: 00:00:00.01
  65. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPADDRERR'  ,'60');
  66.  
  67. 1 row created.
  68.  
  69. Elapsed: 00:00:00.01
  70. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'IPPROTERR'  ,'28');
  71.  
  72. 1 row created.
  73.  
  74. Elapsed: 00:00:00.01
  75. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'STATUS'     ,'K');
  76.  
  77. 1 row created.
  78.  
  79. Elapsed: 00:00:00.01
  80. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'TYPE'       ,'LIM');
  81.  
  82. 1 row created.
  83.  
  84. Elapsed: 00:00:00.01
  85. chris@XE> insert into t values('avl-stplan', 'tahlt02', 'EAGLE537', to_date('08/11/2007','dd/mm/yyyy'), 'LOC'        ,'1209');
  86.  
  87. 1 row created.
  88.  
  89. Elapsed: 00:00:00.01
  90. chris@XE>
  91. chris@XE> commit;
  92.  
  93. Commit complete.
  94.  
  95. Elapsed: 00:00:00.01
  96. chris@XE>
  97. chris@XE> select * from t
  98.   2
  99. chris@XE> SELECT   logtype,
  100.   2           clli,
  101.   3           swrel,
  102.   4           rptdate,
  103.   5           MAX (DECODE (cname,'TCPRSTSENT', cvalue)) tcprstsent,
  104.   6           MAX (DECODE (cname,'IPHDRERR', cvalue)) IPHDRERR,
  105.   7           MAX (DECODE (cname,'IPADDRERR', cvalue)) IPADDRERR,
  106.   8           MAX (DECODE (cname,'IPPROTERR', cvalue)) IPPROTERR,
  107.   9           MAX (DECODE (cname,'STATUS', cvalue)) STATUS,
  108.  10           MAX (DECODE (cname,'TYPE', cvalue)) TYPE,
  109.  11           MAX (DECODE (cname,'LOC', cvalue)) LOC
  110.  12  FROM     t
  111.  13  GROUP BY logtype,
  112.  14           clli,
  113.  15           swrel,
  114.  16           rptdate;
  115.  
  116. LOGTYPE     CLLI     SWREL     RPTDATE      TCPRSTSENT  IPHDRERR  IPADDRERR  IPPROTERR  STATUS  TYPE  LOC
  117. ----------- -------- --------- ------------ ----------- --------- ---------- ---------- ------- ----- -----
  118. avl-stplan  tahlt02  EAGLE537  08-NOV-2007  40          22        60         28         K       LIM   1209
  119. avl-stplan  tahlt01  EAGLE537  08-NOV-2007  12          88        65         34         N       LIM   1216
  120.  
  121. Elapsed: 00:00:00.03
  122. chris@XE>
  123.  
Dec 25 '07 #2

debasisdas
Expert 5K+
P: 8,127
Please find a related discussion here and few others in the How To section of Oracle.
Dec 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.