Home » SQL & PL/SQL » SQL & PL/SQL » Tricky query (Oracle 10.2.0.1, XP)
Tricky query [message #396383] Mon, 06 April 2009 07:02 Go to next message
prachi.jain8
Messages: 5
Registered: April 2009
Location: India
Junior Member
Dear Sir/Madam,

Could you please give a solution to this?

Select * from student_rec;

Roll TotalMarks
---- ----------
1 80
2 70
3 60
4 82
5 84
6 66
7 66
8 90
9 77
10 64
11 54
12 69
13 53
14 77
15 71
16 84
17 98
18 44
19 50
20 41

The above are the total marks obtained by the students against roll number. But the output should be displayed based on roll number range and in horizontal row.

e.g,


When I want result from roll number 1 to 9 the result should be displayed like below:


1 2 3 4 5 6 7 8 9
80 70 60 82 84 66 66 90 77



when I want result from roll number 5 to 9 the result should be displayed like below:

5 6 7 8 9
84 66 66 90 77



when I want result from roll number 4 to 9 the result should be displayed like below:

4 5 6 7 8 9
82 84 66 66 90 77

and so on..




Thank you!
Re: Tricky query [message #396387 is a reply to message #396383] Mon, 06 April 2009 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not tricky just standard pivot SQL you could find if you have searched.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel

[Updated on: Mon, 06 April 2009 07:22]

Report message to a moderator

Re: Tricky query [message #396389 is a reply to message #396387] Mon, 06 April 2009 07:28 Go to previous messageGo to next message
prachi.jain8
Messages: 5
Registered: April 2009
Location: India
Junior Member
Dear Sir,

I am formatting it again. I got the pivot sql. But my criteria is a bit different. Here, the output is based on roll number range.

Could you please give a solution to this?
Select * from student_rec;

Roll TotalMarks
---- ----------
1 80
2 70
3 60
4 82
5 84
6 66
7 66
8 90
9 77
10 64
11 54
12 69
13 53
14 77
15 71
16 84
17 98
18 44
19 50
20 41

The above are the total marks obtained by the students against roll number. But the output should be displayed based on roll number range and in horizontal row.

e.g,


When I want result from roll number 1 to 9 the result should be displayed like below:

1 2 3 4 5 6 7 8 9
80 70 60 82 84 66 66 90 77



when I want result from roll number 5 to 9 the result should be displayed like below:
5 6 7 8 9
84 66 66 90 77



when I want result from roll number 4 to 9 the result should be displayed like below:
4 5 6 7 8 9
82 84 66 66 90 77

and so on..




Thank you!

[Updated on: Mon, 06 April 2009 07:29]

Report message to a moderator

Re: Tricky query [message #396394 is a reply to message #396389] Mon, 06 April 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Formatting also means aligning columns. Do you really generate and format your reports like this?

The answer is the same, the (about) same question is posted each week, for instance by delna last week.

Regards
Michel
Re: Tricky query [message #396401 is a reply to message #396394] Mon, 06 April 2009 08:09 Go to previous messageGo to next message
prachi.jain8
Messages: 5
Registered: April 2009
Location: India
Junior Member
Sir,

Sorry if it leads to trouble for you to understand as it is not formatted well. My apologies.


I saw delna's post. But it is for a fixed columns. But here I want
to generate based on roll number range and this should be displayed like that.




Select * from student_rec;

Roll				TotalMarks
----				----------
1					80
2					70
3					60
4					82
5					84
6					66
7					66
8					90
9					77
10					64
11					54
12					69
13					53
14					77
15					71
16					84
17					98	
18					44
19					50
20					41


The above are the total marks obtained by the students against roll number. But the output should be displayed based on roll number range
and in horizontal row.

e.g,


When I want result from roll number 1 to 9 the result should be displayed like below:


1	2	3	4	5	6	7	8	9
80	70	60	82	84	66	66	90	77



when I want result from roll number 5 to 9 the result should be displayed like below:

5	6	7	8	9
84	66	66	90	77



when I want result from roll number 4 to 9 the result should be displayed like below:

4       	5	    6	    7	  8	 9
82	       84	   66	   66	 90	 77

[Updated on: Mon, 06 April 2009 08:10]

Report message to a moderator

Re: Tricky query [message #396404 is a reply to message #396401] Mon, 06 April 2009 08:12 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
It is not tricky just standard pivot SQL you could find if you have searched


Have you tried using pivot method ??

Babu
Re: Tricky query [message #396406 is a reply to message #396404] Mon, 06 April 2009 08:17 Go to previous messageGo to next message
prachi.jain8
Messages: 5
Registered: April 2009
Location: India
Junior Member
Yes sir. I can do it for fixed columns.I understood the method . But no idea how to filter the output for the different rollno range.

Re: Tricky query [message #396409 is a reply to message #396401] Mon, 06 April 2009 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Those who post a Test case (create table and insert statements) have better chance to get a full answer and example.

Regards
Michel
Re: Tricky query [message #396425 is a reply to message #396409] Mon, 06 April 2009 10:31 Go to previous messageGo to next message
prachi.jain8
Messages: 5
Registered: April 2009
Location: India
Junior Member
Sir, sorry for the delay in reply. Here it is

Create table student_rec
( Roll   number,
  TotalMarks  number);

insert into student_rec values( 1, 80);
insert into student_rec values( 2, 70);
insert into student_rec values( 3, 60);
insert into student_rec values( 4, 82);
insert into student_rec values( 5, 84);
insert into student_rec values( 6, 66);
insert into student_rec values( 7, 66);
insert into student_rec values( 8, 90);
insert into student_rec values( 9, 77);
...
insert into student_rec values( 20, 41);

[Updated on: Mon, 06 April 2009 10:32]

Report message to a moderator

Re: Tricky query [message #396441 is a reply to message #396425] Mon, 06 April 2009 12:59 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col ord noprint
SQL> set head off
SQL> def beg=1
SQL> def end=9
SQL> with cols as ( select level col from dual connect by level <= &end-&beg+1 )
  2  select 1 ord,
  3         replace(substr(sys_connect_by_path(to_char(roll,'999'),'/'),2),'/',' ') val
  4  from student_rec
  5  where roll = &end
  6  connect by prior roll = roll-1
  7  start with roll = &beg
  8  union all
  9  select 2 ord,
 10         substr(sys_connect_by_path('----',' '),2) val
 11  from cols
 12  where col = &end-&beg+1
 13  connect by prior col = col-1
 14  start with col = 1
 15  union all
 16  select 3 ord,
 17         replace(substr(sys_connect_by_path(to_char(TotalMarks,'999'),'/'),2),'/',' ') val
 18  from student_rec
 19  where roll = &end
 20  connect by prior roll = roll-1
 21  start with roll = &beg
 22  order by 1
 23  /
   1    2    3    4    5    6    7    8    9
---- ---- ---- ---- ---- ---- ---- ---- ----
  80   70   60   82   84   66   66   90   77

3 rows selected.

SQL> def beg=5
SQL> /
   5    6    7    8    9
---- ---- ---- ---- ----
  84   66   66   90   77

3 rows selected.

SQL> def beg=4
SQL> /
   4    5    6    7    8    9
---- ---- ---- ---- ---- ----
  82   84   66   66   90   77

3 rows selected.

Regards
Michel
Previous Topic: Split a string
Next Topic: Number format with Decimals (merged)
Goto Forum:
  


Current Time: Thu Dec 08 22:25:43 CST 2016

Total time taken to generate the page: 0.08718 seconds