question on 10g analytic functions

Giganews Newsgroups
Subject: question on 10g analytic functions
Posted by:  GuangMei (zlm…@hotmail.com)
Date: Tue, 22 Jul 2008

This is a question on 10g analytic functions

I have Table T1 with four number columns:

Table T1:
id    X    Y    Z
1    0    0    0
1    0    1    0
2    0    1    1
2    1    1    1
3    0    0    1
3    0    1    1
3    1    0    1

After group by "id:, I get:

For X=0:
id    Count1
1    0
2    1
3    1

For Y=0:
id    Count2
1    1
2    2
3    1

For Z=0:
id    Count3
1    0
2    2
3    3

I want to create a new table T1_CNT which shows:

id      Count1    Count2    Count3
1    0    1    0
2    1    2    2
3    1    1    3

I can do these in multiple steps. But I would like to create T1_CNT
from T1 directly, maybe using some 10g analytic functions.
Any idea?

TIA.
Guang

Replies