group [message #301155] |
Tue, 19 February 2008 06:10 |
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 |
|
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 #301285 is a reply to message #301155] |
Tue, 19 February 2008 23:54 |
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
|
|
|