Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Query using connect by in 10g (Oracle 10g)
Pivot Query using connect by in 10g [message #416222] Fri, 31 July 2009 04:48 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

How to write pivot query in Oracle 10 g using connect by clause .Thanks in Advance
Re: Pivot Query using connect by in 10g [message #416240 is a reply to message #416222] Fri, 31 July 2009 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your question is meaningless.
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Pivot Query using connect by in 10g [message #416247 is a reply to message #416240] Fri, 31 July 2009 06:23 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


CREATE TABLE TESTT
(
  N_ID      NUMBER(10),
  S_DESC    VARCHAR2(100 BYTE),
  N_COUNT   NUMBER(10),
  C_STATUS  CHAR(1 BYTE)       DEFAULT 'Y'
)

Insert into TESTT
   (N_ID, S_DESC, N_COUNT, C_STATUS)
 Values    (19, 'Production', 2 , 'Y');
Insert into TESTT
   (N_ID, S_DESC, N_COUNT, C_STATUS)
 Values    (1, 'TimeTaken', 85, 'Y');
Insert into TESTT
   (N_ID, S_DESC, N_COUNT, C_STATUS)
 Values    (2, 'Remarks', 28, 'Y');
Insert into TESTT
   (N_ID, S_DESC, N_COUNT, C_STATUS)
 Values    (3, 'EndDate', 7, 'Y');
Insert into TESTT
   (N_ID, S_DESC, N_COUNT, C_STATUS)
 Values    (4, 'StartDate',  1, 'Y');
COMMIT;

SELECT * FROM TESTT



N_ID S_DESC N_COUNT C_STATUS

19 Production 2 Y
1 TimeTaken 85 Y
2 Remarks 28 Y
3 EndDate 7 Y
4 StartDate 1 Y

THE OUTPUT WHAT I NEED IS

N_ID Production TimeTaken Remarks EndDate StartDate

19 2 85 28 7 1
1 2 85 28 7 1
2 2 85 28 7 1
3 2 85 28 7 1
4 2 85 28 7 1

I want it dynamically ,as records increase column should also increase .
Re: Pivot Query using connect by in 10g [message #416250 is a reply to message #416247] Fri, 31 July 2009 06:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
THE OUTPUT WHAT I NEED IS


can you explain the logic of how you want your output?
I see this pattern
- 2 85 28 7 1
- 2 85 28 7 1 
- 2 85 28 7 1
- 2 85 28 7 1
- 2 85 28 7 1

[Updated on: Fri, 31 July 2009 06:35]

Report message to a moderator

Re: Pivot Query using connect by in 10g [message #416254 is a reply to message #416250] Fri, 31 July 2009 06:44 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

N_ID Production TimeTaken Remarks EndDate StartDate

19     2           85      28         7        1
1      2           85      28         7        1
2      2           85      28         7        1
3      2           85      28         7        1
4      2           85      28         7         1 
Re: Pivot Query using connect by in 10g [message #416258 is a reply to message #416254] Fri, 31 July 2009 07:01 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740
for quick reference

[Updated on: Fri, 31 July 2009 07:02]

Report message to a moderator

Re: Pivot Query using connect by in 10g [message #416262 is a reply to message #416254] Fri, 31 July 2009 07:16 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
  select n_id 
  ,(select n_count from (select  n_count,rownum r from testt) where r=1 ) a
  ,(select n_count from (select  n_count,rownum r from testt) where r=2 ) b
  ,(select n_count from (select  n_count,rownum r from testt) where r=3 ) c
  ,(select n_count from (select  n_count,rownum r from testt) where r=4 ) d
  ,(select n_count from (select  n_count,rownum r from testt) where r=5 ) e
  from testt


this serves your purpose but for increasing number of columns I think you must be knowing how many maximum values can be there if you want to solve your problem using static sql.In other case you still have procedural capabilities of PL/sql at your footstep and you are really free to use dynamic sql.
Re: Pivot Query using connect by in 10g [message #416263 is a reply to message #416254] Fri, 31 July 2009 07:16 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rakeshramm wrote on Fri, 31 July 2009 13:44
N_ID Production TimeTaken Remarks EndDate StartDate

19     2           85      28         7        1
1      2           85      28         7        1
2      2           85      28         7        1
3      2           85      28         7        1
4      2           85      28         7         1 


Quote:
can you explain the logic of how you want your output?

Can you?

Regards
Michel

Previous Topic: How to tune/reduce time for this simple procedure ?
Next Topic: Find max records in a group
Goto Forum:
  


Current Time: Wed Dec 07 22:33:51 CST 2016

Total time taken to generate the page: 0.27121 seconds