OraFAQ Forum: SQL & PL/SQL » change in data format by query for data extraction

Home » SQL & PL/SQL » SQL & PL/SQL » change in data format by query for data extraction

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

change in data format by query for data extraction [message #268989]

Thu, 20 September 2007 06:42

sonalshastry
Messages: 52
Registered: September 2007

Member

Hi everyone

i have a query by which i retrive clint_Id the column name is ent_id and the sum of brokerage monthwise
for brokerage
my data comes like this :

   ent_id       month          brokerage
1  140155	APR2007	       829.51
2  140155	JUN2007	       750.00
3  140155	MAY2007	       761.2

but now i want to disply same data in this format given below

   ent_id       APR2007    JUN2007     MAY2007      
1  140155	829.51	   750.00      761.2

if i use case statement then what is the right code because
what i have written is not giving me the brokerage amount
only give month
here is my query

SELECT  EM.ENT_ID , 
CASE WHEN  TO_CHAR(CNM.CNM_DATE,'MMYYYYY') = 'APR2007' THEN sum(CNM.CNM_BROKERAGE_AMT) END APRIL,
CASE WHEN  TO_CHAR(CNM.CNM_DATE,'MMYYYYY') = 'MAY2007' THEN SUM(CNM.CNM_BROKERAGE_AMT) END MAY 
FROM ENTITY_MASTER EM, CONTRACT_NOTE_MASTER CNM   
WHERE EM.ent_id = CNM.cnm_ent_id
and EM.ent_id 114155
And CNM.cnm_date >= '01-april-2007' 
and CNM.cnm_date <= '30-MAY-2007'
GROUP BY EM.ENT_ID,TO_CHAR(CNM.CNM_DATE,'MMYYYYY')

please rectify my mistake in this

thanks in advance

Sonal

Report message to a moderator