question on 10g analytic functions

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 Received on Tue Jul 22 2008 - 19:50:29 CDT