Home » SQL & PL/SQL » SQL & PL/SQL » Vertical Pivot query (merged 3)
Vertical Pivot query (merged 3) [message #393866] Tue, 24 March 2009 14:38 Go to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Hi,

I am trying to convert rows into columns using vertical pivot query

now the issue is I want to store these column values in a temporary table and more over there are 128 rows.

It is quite tedious to write to SQL for this

I am new to Oracle your help will be appreciated.


the query in simple world would be

here is the query this gives me the desired result set but want to simply the query writing by using stored procedure and store all the column values into a temporary table TABle12.


Insert into TABLE12 (p1,p2,p3)
SELECT MAX(CASE WHEN ROWNUM = 1
THEN Name END) AS P1,
MAX(CASE WHEN ROWNUM = 2
THEN Name END) AS P2,
MAX(CASE WHEN ROWNUM = 3
THEN Name END) AS P3

From Table1


This query works fine I am not sure how to write a stored procedure for this as

I did try this

CREATE OR REPLACE PROCEDURE LOAD_TAble is

BEGIN
declare
i number := 1;
cnt1 varchar2(9);

begin
While i < 5 LOOP
execute immediate 'Insert into TABLE12 (p'||i||') SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN Name END) AS P'||i||' FROM TABLE1 ;
i := i+1;
END LOOP;
commit;
end;

END;


But the issue is the desired o\p is not in one row


it is as follows:


P1 P2 P3 P4

AA
BB
CC
DD


where as If I execute this

Insert into TABLE12 (p1,p2,p3)
SELECT MAX(CASE WHEN ROWNUM = 1
THEN Name END) AS P1,
MAX(CASE WHEN ROWNUM = 2
THEN Name END) AS P2,
MAX(CASE WHEN ROWNUM = 3
THEN Name END) AS P3
MAX(CASE WHEN ROWNUM = 4
THEN Name END) AS P4
From Table1


I get the o\p as

P1 P2 P3 P4
AA BB CC DD


which is the desired o\p

I am just missing some hint.




Thanks In Advance for your time and paitence
Re: Vertical Pivot query [message #393867 is a reply to message #393866] Tue, 24 March 2009 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Vertical Pivot query [message #393868 is a reply to message #393866] Tue, 24 March 2009 14:53 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Hi,

I am trying to convert rows into columns using vertical pivot query

now the issue is I want to store these coulmn values in a temporary table and more over there are 128 rows.

It is quite tedious to write to SQl for this

I am new to Oracle your help will be appreciated.


the query in simple world would be (I have just been able to derive the select statement would like to konw how to insert also into a temp table.)


here is the query this gives me the desired result set but want to simply the query writing by using stored procedure and store all the column values into a temporary table.


INSERT INTO TABLE12 values (p1,p2,p3)
SELECT MAX(CASE WHEN ROWNUM = 1
THEN NAME END) AS P1,
MAX(CASE WHEN ROWNUM = 2
THEN NAME END) AS P2,
MAX(CASE WHEN ROWNUM = 3
THEN NAME END) AS P3
from Table


The desired o\p is

P1 P2 P3
AA BB CC




I have written one stored proc to do this:


CREATE OR REPLACE PROCEDURE LOAD_WXDM_DATE_BUCKET(v_tbl_nm in varchar2, v_db_schema varchar2) AS

BEGIN
declare
i number := 1;
cnt1 varchar2(9);

begin
While i < 4 LOOP

execute immediate 'INSERT INTO TABLE12 (P'||i||' ) SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN NAME END) AS P'||i||' FROM Table

i := i+1;
END LOOP;
commit;
end;

END;
/

But this proc gives output as


P1 P2 P3

AA
NULL BB

NUll Null CC



that is 3 rows which is not desirable
I want all values in one single row.

Please Advice


Thanks In Advance
Re: Vertical Pivot query [message #393869 is a reply to message #393866] Tue, 24 March 2009 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>now the issue is I want to store these column values in a temporary table and more over there are 128 rows.
Will the solution always & forever more consist of exactly 128 rows?
As a general rule this is a bad idea.
Perhaps if you described what problem you are really trying to solve, someone here might be able to propose a more elegant solution.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Try SEARCH this forum for "PIVOT".

[Updated on: Tue, 24 March 2009 14:58]

Report message to a moderator

Re: Vertical Pivot query [message #393870 is a reply to message #393868] Tue, 24 March 2009 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Do NOT cross/multi-post
Vertical Pivot query [message #393871 is a reply to message #393866] Tue, 24 March 2009 15:01 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Hi,

I am trying to convert rows into columns using vertical pivot query

now the issue is I want to store these column values in a temporary table and more over there are 128 rows.

It is quite tedious to write to SQl for this

Here is the query this gives me the desired result set but want to simply the query writing by using stored procedure and store all the column values into a temporary table.


INSERT INTO TABLE12 values (p1,p2,p3)
SELECT MAX(CASE WHEN ROWNUM = 1
THEN NAME END) AS P1,
MAX(CASE WHEN ROWNUM = 2
THEN NAME END) AS P2,
MAX(CASE WHEN ROWNUM = 3
THEN NAME END) AS P3
from Table


The desired o\p is

P1 P2 P3
AA BB CC




I have written one stored proc to do this:


CREATE OR REPLACE PROCEDURE LOAD_WXDM_DATE_BUCKET(v_tbl_nm in varchar2, v_db_schema varchar2) AS

BEGIN
declare
i number := 1;
cnt1 varchar2(9);

begin
While i < 4 LOOP

execute immediate 'INSERT INTO TABLE12 (P'||i||' ) SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN NAME END) AS P'||i||' FROM Table

i := i+1;
END LOOP;
commit;
end;

END;
/

But this proc gives output as


P1 P2 P3

AA
BB

CC


I want all values in one single row.

Please Advice


Thanks In Advance
Re: Vertical Pivot query [message #393873 is a reply to message #393871] Tue, 24 March 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
stop SPAMMING!
Re: Vertical Pivot query [message #393876 is a reply to message #393871] Tue, 24 March 2009 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 24 March 2009 20:44
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Vertical Pivot query [message #393878 is a reply to message #393869] Tue, 24 March 2009 15:11 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
I did search the forum but the issue is I am getting data as

from my stored procedure as


P1 P2 P3

AA

NULL BB
Null Null CC


where as I want it to be as follows

P1 P2 P3
AA BB CC


I am missing some thing in my stored proc

the step which will help me get to this

I can insert values into Table12 after the entire selection is done

then I should assign the values to different variables in the loop that is

CREATE OR REPLACE PROCEDURE LOAD_TAble is

BEGIN
declare
i number := 1;
cnt1 varchar2(9);

begin
While i < 5 LOOP
execute immediate 'SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN Name END) AS P'||i||' FROM TABLE1 into 'cnt'||i;
i := i+1;
END LOOP;

execute immediate 'Insert into TABLE12 values (Cnt1,cnt2,...cnt4)
commit;
end;

END;

but 'cnt'||i doesnot work.

how do I achieve this.

Please guide


Re: Vertical Pivot query [message #393879 is a reply to message #393878] Tue, 24 March 2009 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 24 March 2009 21:09
Michel Cadot wrote on Tue, 24 March 2009 20:44
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel



Re: Vertical Pivot query (merged 3) [message #393883 is a reply to message #393866] Tue, 24 March 2009 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>but 'cnt'||i doesnot work.
Above is not a valid Oracle error code/message.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Vertical Pivot query (merged 3) [message #393885 is a reply to message #393883] Tue, 24 March 2009 15:31 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Yes ,

I am aware that above is not valid

can I have some technique where I can assign values to variable like cnt1

then when the value of i is 2 I can assign value to cnt2

this would really be helpful

Thanks for your Time and Patience
Re: Vertical Pivot query (merged 3) [message #393886 is a reply to message #393866] Tue, 24 March 2009 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Re: Vertical Pivot query (merged 3) [message #393888 is a reply to message #393886] Tue, 24 March 2009 15:51 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Hi,

I am really sorry about that please find my issue described in simple terms.(Hope it is as per standards I couldn't use SQL formater sorry about that)

I have two tables:

Create Table
(Name varchar(9))

Create Table12
(P1 varchar(9),
P2 varchar(9),
P3 varchar(9)
)



Insert into Table values ('AA')
Insert into Table values ('BB')
Insert into Table values ('CC')



select * from Table

Name

AA

BB

CC


I have a written a stored proc


CREATE OR REPLACE PROCEDURE LOAD_TAble is

BEGIN
declare
i number := 1;
cnt1 varchar2(9);

begin

While i < 4 LOOP
execute immediate 'Insert into TABLE12 (p'||i||') SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN Name END) AS P'||i||' FROM TABLE1 ;
i := i+1;
END LOOP;
commit;
end;

END;



O\p is

P1 P2 P3
AA Null Null
Null BB Null
Null Null CC


where as the desired output is

P1 P2 P3
AA BB CC



Please Help me achieve the desired o\p

Thanks In Advance for your time and Patience
Re: Vertical Pivot query (merged 3) [message #393889 is a reply to message #393888] Tue, 24 March 2009 16:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hope it is as per standards I couldn't use SQL formater sorry about that)

Are you unable to read and understand the links? What is not clear? Explain us we can then enhance the guide.
And you still don't popst your Oracle I CLEARLY asked 3 times.

SQL> Create Table
  2  (Name varchar(9))
  3  /
(Name varchar(9))
*
ERROR at line 2:
ORA-00903: invalid table name

This is not a working test case.

SQL> Create Table T
  2  (Name varchar(9))
  3  /

Table created.

SQL> Insert into t  values ('AA')
  2  /

1 row created.

SQL> Insert into t  values ('BB');

1 row created.

SQL> Insert into t  values ('CC');

1 row created.

SQL> with data as (select rownum line, name from t)
  2  select max(decode(line,1,name)) p1,
  3         max(decode(line,2,name)) p2,
  4         max(decode(line,3,name)) p3
  5  from data
  6  /
P1        P2        P3
--------- --------- ---------
AA        BB        CC

1 row selected.

Regards
Michel


[Updated on: Tue, 24 March 2009 16:02]

Report message to a moderator

Re: Vertical Pivot query (merged 3) [message #393895 is a reply to message #393889] Tue, 24 March 2009 17:24 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Sorry about not following the standards.


Michel,

I have total 128 rows in the Table T which you have created in an example .

Your code gives the desired o\p can we achieve this using PL\SQL


Thanks For your Time and Patience
Re: Vertical Pivot query (merged 3) [message #393896 is a reply to message #393866] Tue, 24 March 2009 17:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>now the issue is I want to store these column values in a temporary table and more over there are 128 rows.
Will the solution always & forever more consist of exactly 128 rows?
As a general rule this is a bad idea.
Perhaps if you described what problem you are really trying to solve, someone here might be able to propose a more elegant solution.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Try SEARCH this forum for "PIVOT".


Re: Vertical Pivot query (merged 3) [message #393897 is a reply to message #393896] Tue, 24 March 2009 17:35 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Sir,

As I have mentioned in the last post I would like to have the desired o\p for 128 rows in Table T

It is very tedious to write sql for 128 rows

Is there a way I can write Pl\SQl to get the desired o\p

I have explained In previous post about my attempts to write stored proc and the issue faced with it.


Please guide me with new approach or help me by modifying the Stored proc worked initially to get the desired O\p


Thanks In Advance
Re: Vertical Pivot query [message #393899 is a reply to message #393879] Tue, 24 March 2009 17:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Tue, 24 March 2009 13:11
Michel Cadot wrote on Tue, 24 March 2009 21:09
Michel Cadot wrote on Tue, 24 March 2009 20:44
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel





Post a working Test case: create table and insert statements along with the result you want with these data.

Post a working Test case: create table and insert statements along with the result you want with these data.
Post a working Test case: create table and insert statements along with the result you want with these data.

If you are unwilling to make it easy for us to help you,
then why should we spend our time providing you a solution?

You can write SQL which writes SQL to solve your problem.

>It is very tedious to write sql for 128 rows
You made the table & have to live with it
or change the design.

Re: Vertical Pivot query [message #393900 is a reply to message #393899] Tue, 24 March 2009 18:10 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member

If one refers to previous post they can get the conversation each post can't be fresh from start I am not sure how have I violated the guidelines here.

Frankly telling

I heard that this is very active forum thanks alot for your help in past and present.
Re: Vertical Pivot query (merged 3) [message #393901 is a reply to message #393866] Tue, 24 March 2009 18:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Invoke follow URL previously provided & provided again below
http://www.orafaq.com/forum/t/88153/0/
Next open the section entitled "Posting Guidelines".
Scroll down to the section labeled "Practice".
There are FIVE lines starting with BIG BOLD DOTS

Please make two lists on your next post.
#1 list which guidelines you have previous provided
2# list which guidelines you have NOT previous provided


SEARCH this forum for "PIVOT"

[Updated on: Tue, 24 March 2009 18:51]

Report message to a moderator

Re: Vertical Pivot query (merged 3) [message #394030 is a reply to message #393901] Wed, 25 March 2009 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member

I have to ask:

Are you two actively trying to drive new people away from this forum, or are somehow completely blind to the way that your posts come across?

I grant you, the OP hasn't produced the best quality posts ever, and his test case did have errors in it, but if this offends you so much then JUST DON'T REPLY

If you're going to go to the bother of replying, then actually go that extra yard, and help him solve the problem, rather than nagging him until he's restated his problem in what you feel to be the ideal form.

Neither of you have actually addressed the problem he's got:
Quote:
I am trying to convert rows into columns using vertical pivot query

now the issue is I want to store these column values in a temporary table and more over there are 128 rows.

It is quite tedious to write to SQL for this

Quote:
It is very tedious to write sql for 128 rows

Is there a way I can write Pl\SQl to get the desired o\p

other than to say
Quote:
You can write SQL which writes SQL to solve your problem.
which, as he's already said he's new to Oracle, isn't going to help him, is it??

His opening post shows that he knows the basics of writing a pivot - he just needs someone to show him how to extend this without writing all the columns by hand.

Now, is one of you two actually going to do that, or is this going to be yet another thread where you nag a newbie OP about posting style, while including hints that they lack the skill to implement, until someone else takes pity on the OP and actually solves the problem for them?
[/RANT]
Re: Vertical Pivot query (merged 3) [message #394036 is a reply to message #394030] Wed, 25 March 2009 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/1597/0/

Re: Vertical Pivot query (merged 3) [message #394048 is a reply to message #394036] Wed, 25 March 2009 09:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I must say that JRowbottoms "blablabla" as you seem to qualify it adds a lot more to the forum than constants rants against new members
Re: Vertical Pivot query [message #394060 is a reply to message #393900] Wed, 25 March 2009 10:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740

And search for the post dated "Pivoting example using ref cursor December 31, 2003 - 2pm"

I hope it answers your question how to do it.

Regards

Raj
Re: Vertical Pivot query [message #394077 is a reply to message #394060] Wed, 25 March 2009 11:26 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Thanks Raj,

For the response.

I did search the post and the link passed to you frankly telling this is an urgent requirement for me can you. Please help me.

There are two tables
1)T
2)T12

/***********************************************************************

SQL> Create Table T
2 (Name varchar(9))
3 /

Table created.

SQL> Create Table T12
2 (P1 varchar(9),
3 P2 varchar(9),
4 P3 varchar(9))
5 /

Table created.


SQL> Insert into t values ('AA')
2 /

1 row created.

SQL> Insert into t values ('BB');

1 row created.

SQL> Insert into t values ('CC');

1 row created.

SQL> SELECT * FROM T


NAME
---------
AA
BB
CC

3 ROWS SELECTED

/**************************************************************************************************/

The SQL query :

INSERT INTO TABLE12 values (p1,p2,p3)
SELECT MAX(CASE WHEN ROWNUM = 1
THEN NAME END) AS P1,
MAX(CASE WHEN ROWNUM = 2
THEN NAME END) AS P2,
MAX(CASE WHEN ROWNUM = 3
THEN NAME END) AS P3
from T


The desired o\p is

P1 P2 P3
AA BB CC

As I have about 128 rows in the table T and it is very tedious to write sql for this requirement.

I have written a very simple stored proc to do this:

/************************************************************
CREATE OR REPLACE PROCEDURE LOAD_DATA_BUCKET(v_tbl_nm in varchar2, v_db_schema varchar2) AS

BEGIN
declare
i number := 1;

begin
While i < 4 LOOP

execute immediate 'INSERT INTO TABLE T12(P'||i||'
)SELECT MAX(CASE WHEN ROWNUM = '||i||' THEN NAME
END) AS P'||i||' FROM Table T

i := i+1;
END LOOP;
commit;
end;

END;
/

/******************************************************************
O\p of this stored proc is :

P1 P2 P3
AA Null Null
Null BB Null
Null Null CC

The values were placed in three different rows.


Where as the desired output is


P1 P2 P3
AA BB CC


Please help

Thanks In Advance

Re: Vertical Pivot query [message #394084 is a reply to message #394077] Wed, 25 March 2009 12:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you need to do, as @Michel alluded to is to get SQL (or Pl/Sql) to write your sql for you.

Here's your LOAD_DATA_BUCKET procedure rewritten to use 'self writing' SQL:
 CREATE OR REPLACE PROCEDURE LOAD_DATA_BUCKET (p_tabname  in  varchar2, p_cols  number) AS
 
   v_sql      varchar2(32767);
   v_column   varchar2(30);
   v_insert   varchar2(32767);
   v_columns  varchar2(32767);
   v_from     varchar2(32767);
 BEGIN
 
   v_insert  := 'INSERT INTO '||p_tabname||' (';
   v_columns := ') SELECT ';
   
   v_from    := ' FROM (SELECT rownum as rnum, name FROM t) WHERE rnum <= '||p_cols;
 
 for i in 1..p_cols LOOP
   select column_name
   into   v_column
   from   user_tab_columns
   where  table_name = p_tabname
   and    column_id = i;
 
   if i>1 then
     v_insert  := v_insert||',';
     v_columns := v_columns||',';
   end if;
   
   v_insert  := v_insert ||v_column;
   v_columns := v_columns || 'MAX(CASE WHEN rnum = '||i||' THEN NAME END) ';
   
   --dbms_output.put_line(v_insert);
   --dbms_output.put_line(v_columns);
 end loop;
 
 v_sql := v_insert||v_columns||v_from;
  
 execute immediate v_sql;
 
 commit;
 
 END;
/


What happens is this:

The code loops through the number of columns specified by p_cols.

For each iteration i of the loop, it adds the i'th column of the table to the list of columns to insert into, stored in v_insert

It then adds a CASE statement to fetch a column value to populate from table T and appends this to v_select.

After looping through the required number of columns, we concatenate v_insert and v_select, and then add on v_from (which contains the FROM and WHERE clauses, and execute the resulting SQL.

If you uncomment the two Dbms_output lines, then you'll see what happens as v_insert and v_columns get built up.

You can test the code with this:
begin
  delete t12;
  load_data_bucket('T12',2);
end;
/

SELECT * FROM T12;
Re: Vertical Pivot query [message #394108 is a reply to message #394084] Wed, 25 March 2009 13:35 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Thanks A bunch!!!

I did try the given procedure.

But in the output it says no rows returned.

Please guide


./fa/5954/0/

[Updated on: Wed, 25 March 2009 14:57]

Report message to a moderator

Re: Vertical Pivot query [message #394236 is a reply to message #394108] Thu, 26 March 2009 04:42 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmm - I don't get that.
SQL> select * from T;

NAME
---------
AA
BB
CC

SQL> 
SQL> begin
  2    delete t12;
  3    load_data_bucket('T12',3);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from T12;

P1        P2        P3
--------- --------- ---------
AA        BB        CC

Can you post the SQL*Plus session showing where you ran this?
Previous Topic: How to track which transaction is taking, much time in oracle 9i
Next Topic: how to generate the batch number based on number of records
Goto Forum:
  


Current Time: Sun Dec 04 23:08:26 CST 2016

Total time taken to generate the page: 0.28768 seconds