Home » SQL & PL/SQL » SQL & PL/SQL » change in data format by query for data extraction
change in data format by query for data extraction [message #268989] Thu, 20 September 2007 06:42 Go to next message
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




Re: change in data format by query for data extraction [message #268997 is a reply to message #268989] Thu, 20 September 2007 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want to use a
SUM(CASE WHEN... END)
structure rather than a
CASE WHEN... THEN SUM(...) END
Re: change in data format by query for data extraction [message #268998 is a reply to message #268989] Thu, 20 September 2007 07:01 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

search for pivot.you will find many examples in this forum.


regards,
Previous Topic: Insert into CLOB and select
Next Topic: count of all tables in a schema
Goto Forum:
  


Current Time: Sat Dec 03 11:54:11 CST 2016

Total time taken to generate the page: 0.04415 seconds