| transpose [message #39300] |
Wed, 03 July 2002 23:42  |
Ashok Bhatia
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
The data is currently as follows
A/c no. TimePeriod Count
001 Jan2000 5
001 Feb2000 7
001 Mar2000 10
002 Jan2000 15
002 Feb2000 17
002 Mar2000 1
and so on .
Now i want the data to look like
A/c no. Jan2000 Feb2000 March2000
001 5 7 10
002 15 17 1
Is it possible to do this .Can u help me out in this.Waiting for replies.
Thanks in advance for your help.
Regards ,
Ashok
|
|
|
|
| Re: transpose [message #39301 is a reply to message #39300] |
Thu, 04 July 2002 00:53  |
Saga
Messages: 51
Registered: April 2002
|
Member |
|
|
Here is one of the solution to your problem. Though it is not the best but i hope it will serve your purpose.
First i assumed your table structure is somewhat of this type
CREATE TABLE test ( acno VARCHAR2(3),
timeperiod VARCHAR2(10) )
So the structure of the query will be somewhat like
SELECT a.acno "A/C no."
,MAX(DECODE(timeperiod,'Jan2000',(SELECT COUNT(DECODE(timeperiod,'Jan2000',timeperiod)) FROM test WHERE acno=a.acno))) "Jan2000"
,MAX(DECODE(timeperiod,'Feb2000',(SELECT COUNT(DECODE(timeperiod,'Feb2000',timeperiod)) FROM test WHERE acno=a.acno))) "Feb2000"
,MAX(DECODE(timeperiod,'Mar2000',(SELECT COUNT(DECODE(timeperiod,'Mar2000',timeperiod)) FROM test WHERE acno=a.acno))) "Mar2000"
FROM test a
GROUP BY acno
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
