| how to select table to become cross table [message #97898] |
Tue, 06 May 2003 22:18  |
uichou
Messages: 2
Registered: May 2003
|
Junior Member |
|
|
hi man:
I have a problem.
How can I select sale_table then become cross_table in Oracle.In SQL DB ,the statement is " transform sum(quota) select sale_man from sale_table broup by sale_man pivot season ".But in oracle How write the SQL statement so that the cross_table can result?
help,thanks ....
example:
sale_table
season sale_man quota
Q1 Tom 3000
Q1 John 3100
Q1 Mary 2500
Q2 Tom 2000
Q2 Mary 3500
Q2 Jhon 3300
Q3 Tom 3200
Q3 Mary 2600
Cross_table
sale_man Q1 Q2 Q3
Tom 3000 2000 3200
John 3100 3300
Mary 2500 3500 2600
|
|
|
|
| Re: how to select table to become cross table [message #97899 is a reply to message #97898] |
Wed, 07 May 2003 01:47  |
uichou
Messages: 2
Registered: May 2003
|
Junior Member |
|
|
hi:
Maybe I have solution , as below SQL statement:
" SELECT sale_quota.sale_man,sub1.q1,sub2.q2,sub3.q3
FROM sale_quota,(SELECT sale_man,quota as Q1 FROM sale_quota where season='Q1'
GROUP BY sale_man ,quota) SUB1
,(SELECT sale_man,quota as Q2 FROM sale_quota where season='Q2'
GROUP BY sale_man,quota) SUB2
,(SELECT sale_man,quota as Q3 FROM sale_quota where season='Q3'
GROUP BY sale_man,quota) SUB3
WHERE upper(sale_quota.sale_man)=upper(SUB1.sale_man(+)) and upper(sale_quota.sale_man)=upper(SUB2.sale_man(+)) and upper(sale_quota.sale_man)=upper(SUB3.sale_man(+))
GROUP BY sale_quota.sale_man , sub1.q1 , sub2.q2,sub3.q3 ; "
if you have better SQL , let me know ..^_^ thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
