Home » SQL & PL/SQL » SQL & PL/SQL » group
group [message #301155] Tue, 19 February 2008 06:10 Go to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
how can i group where ami going wrong
select t2.xxx,t2.yyy,t2.zzz,
(select aaa from t1
where......
t1=t2
)col2,
(select aaa from t1
where......
t1=t2
)col3,
(select aaa from t1
where......
t1=t2
)col4
from 
(select xxx,yyy,zzz
from t2)
order by t2.xxx

iam getting 

xxx	yyy	zzz	col2	col3	col4
101	aaa	bbb	test1		
101	aaa	bbb		test1	
101	aaa	bbb			test1


i want to get 
xxx	yyy	zzz	col2	col3	col4
101	aaa	bbb	test1	test1	test1

where am i going wrong
Re: group [message #301182 is a reply to message #301155] Tue, 19 February 2008 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
where am i going wrong

As always:
- you don't post formatted
- you don't post the corresponding test case (who knows what you have in your tables?)
- you don't search
- you don't post in the correct forum, you are a newbie post in newbie forum

Regards
Michel

[Updated on: Tue, 19 February 2008 07:34]

Report message to a moderator

Re: group [message #301213 is a reply to message #301155] Tue, 19 February 2008 10:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
search for PIVOT
Re: group [message #301285 is a reply to message #301155] Tue, 19 February 2008 23:54 Go to previous message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Sample Pivot for you.

SQL> select deptno,sal from emp order by deptno;
   DEPTNO       SAL
--------- ---------
       10      2450
       10      5000
       10      1300
       20       800
       20      1100
       20      3000
       20      3000
       20      2975
       30      1600
       30      2850
       30      1250
       30       950
       30      1500
       30      1250


SQL> select deptno,
     max(decode(r,1,sal)) "SAL1",
     max(decode(r,2,sal)) "SAL2"
     from
    (select deptno,sal,
     row_number() over (partition by deptno order by deptno) r
     from emp)
     group by deptno
/
   DEPTNO      SAL1      SAL2
--------- --------- ---------
       10      2450      5000
       20       800      1100
       30      1600      2850



Thanks

Mano
Previous Topic: How to achieve Global Temporary Functionality by using the Table
Next Topic: about dates
Goto Forum:
  


Current Time: Sun Nov 03 06:07:23 CST 2024