Home » SQL & PL/SQL » SQL & PL/SQL » need help in query
need help in query [message #341506] Tue, 19 August 2008 02:36 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi all ,.

SQL> select * from test2;

ACCT DOB PRODU
---------- --------- ----------
1 12-MAR-08 1111
2 21-JAN-07 2222
3 21-JAN-07 3333

From the above table i need output like

21-JAN-07 12-MAR-08
5555 1111 ----sum(PRODU)
i mean i want to displacy row values as columns.
Re: need help in query [message #341515 is a reply to message #341506] Tue, 19 August 2008 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide:
1/ Format your post
2/ Post a test case
3/ Post what you already tried
4/ Post your Oracle version (4 decimals)

Regards
Michel
Re: need help in query [message #341516 is a reply to message #341506] Tue, 19 August 2008 03:13 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Michel,

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test2(
2 ACCT number(10),
3 dob DATE,
4 produ number(10));

Table created.

SQL> insert into test2 values (1,'12-MAR-08',1111);

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into test2 values (2,'21-JAN-07',2222)
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into test2 values (3,'21-JAN-07',3333)
SQL> /

1 row created.

SQL> select * from test2;

ACCT DOB PRODU
---------- --------- ----------
1 12-MAR-08 1111
2 21-JAN-07 2222
3 21-JAN-07 3333

I tried by pl/sql block like :

SQL> ed
Wrote file afiedt.buf

1 declare
2 amnt number :=0;
3 cursor c1 is select distinct dob from test2;
4 begin
5 for var in c1 loop
6 --select sum(produ) into amnt from test2 where dob = var.dob;
7 dbms_output.put_line(var.dob || ' ' );
8 end loop;
9* end;

how can i get the desired result ????
Re: need help in query [message #341524 is a reply to message #341516] Tue, 19 August 2008 03:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You have still not formatted the post. This is what @Michael has requested you to do it.
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test2(
2 ACCT number(10),
3 dob DATE,
4 produ number(10));

Table created.

SQL> insert into test2 values (1,'12-MAR-08',1111);

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into test2 values (2,'21-JAN-07',2222)
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into test2 values (3,'21-JAN-07',3333)
SQL> /

1 row created.

SQL> select * from test2;

ACCT DOB PRODU
---------- --------- ----------
1 12-MAR-08 1111
2 21-JAN-07 2222
3 21-JAN-07 3333

I tried by pl/sql block like :

SQL> ed
Wrote file afiedt.buf

1 declare
2 amnt number :=0;
3 cursor c1 is select distinct dob from test2;
4 begin
5 for var in c1 loop
6 --select sum(produ) into amnt from test2 where dob = var.dob;
7 dbms_output.put_line(var.dob || ' ' );
8 end loop;
9* end;

It's nice to see your pl/sql code but you have not posted the output of the pl/sql block.

Anyways, coming back to your question how do you expect the output if you have more than two group of dates. Do you want the dob to appear as a column value or appear as the column heading. Either way it looks to me the most infamous question pivot. Search in this forum, asktom.oracle.com, any other oracle forums for the keywords pivot, rows to columns, stragg you will be bombarded with answers.

Hope that helps.

Regards

Raj

[Edit:] Missed to close the code tag

[Updated on: Tue, 19 August 2008 03:53]

Report message to a moderator

Re: need help in query [message #341535 is a reply to message #341506] Tue, 19 August 2008 04:20 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please post with proper formatting.

For this no need of PLSQL block,
You can do it with a SQL query,

SELECT  	   SUM(DECODE(DOB,'21-JAN-07',PRODU)) "21-JAN-07",
			   SUM(DECODE(DOB,'12-MAR-08',PRODU)) "12-MAR-08"
FROM TEST2

Re: need help in query [message #341540 is a reply to message #341535] Tue, 19 August 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@ora_2007

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: need help in query [message #341541 is a reply to message #341506] Tue, 19 August 2008 04:36 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Next time i will keep this thing in mind. Smile
Re: need help in query [message #341544 is a reply to message #341535] Tue, 19 August 2008 04:48 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thanks Ora,.

But i can't go for hardcoding as i am not sure how many distinct DOB values it will have.

Rajaram i tried to find but did not got the proper solution. Sad

Re: need help in query [message #341556 is a reply to message #341544] Tue, 19 August 2008 06:04 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

I got the answer for my question(For Bank Data base).The query is

SQL> select * from test3;

ACNO ODATE BNAME
---------- --------- ----------
1023 01-JAN-07 Branch 1
2303 01-JAN-07 Branch 2
12494 01-FEB-07 Branch 1
2359 01-FEB-07 Branch 2

SQL> SELECT BNAME "BRANCH NAME",
2 MAX(case when ODATE = '01-JAN-07' then ACNO end ) "01-JAN-07",
3 MAX(case when ODATE = '01-FEB-07' then ACNO end ) "01-FEB-07"
4 from test3
5 group by BNAME
6 /

BRANCH NAM 01-JAN-07 01-FEB-07
---------- ---------- ----------
Branch 2 2303 2359
Branch 1 1023 12494

But any one can pls let me know how can i avoid the hard coding
since if ther r thousand distinct dates the above solution will be poor.
Re: need help in query [message #341560 is a reply to message #341556] Tue, 19 August 2008 06:11 Go to previous message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search on forum, it has been answered last week.
Read forum guide and format your posts.

Regards
Michel
Previous Topic: can some body explin what this pragma does RESTRICT_REFERENCES ?
Next Topic: sqlplus language conversion
Goto Forum:
  


Current Time: Thu Dec 12 08:08:26 CST 2024