write a query to follwoing table
amount year quarter
254556 2003 first
546332 2003 second
129034 2003 third
490223 2003 fourth
165768 2004 first
265443 2004 second
510412 2004 third
435690 2004 fourth
i want the output
year q1_amount q2_amount q3_amount q4_amount
2003 254556 546332 129034 490223
2004 165768 265443 510412 435690
Answers were Sorted based on User's Feedback
Select YEAR,SUM(q1_amt)q1_amt,SUM(q2_amt)q2_amt,SUM(q3_amt)
q3_amt,SUM(q4_amt)q4_amt from (
select YEAR,
Decode (QUARTER,'first',amount,0) as q1_amt,
Decode (QUARTER,'second',amount,0) as q2_amt,
Decode (QUARTER,'third',amount,0) as q3_amt,
Decode (QUARTER,'fourt',amount,0) as q4_amt
from table_name) group by YEAR
Is This Answer Correct ? | 8 Yes | 0 No |
Answer / ankit kansal
The above answer is perfectly fine, however you can achieve the same using case
Select YEAR,SUM(q1_amt)q1_amt,SUM(q2_amt)q2_amt,SUM(q3_amt)
q3_amt,SUM(q4_amt)q4_amt from (
select YEAR,
CASE QUARTER WHEN 'first' THEN amount ELSE 0 END as q1_amt,
CASE QUARTER WHEN 'second' THEN amount ELSE 0 END as q2_amt,
CASE QUARTER WHEN 'third' THEN amount ELSE 0 END as q3_amt,
CASE QUARTER WHEN 'fourth' THEN amount ELSE 0 END as q4_amt
from t_name) temp group by YEAR;
http://deepinopensource.blogspot.in/
Is This Answer Correct ? | 0 Yes | 0 No |
what are the difference between Informatica 7.1 and 8.1?
how can i invoke pmcmd in command line prompt
What do you mean by filter transformation?
What are the Differences between static cache and dynamic cache?
when will we go for unconnected lookup transformation in Informatica?
How do you avoid duplicate records with out using source qualifier,expression,aggrigator,sorter and lookup transformations ?
what are the different types of transformation available in informatica. And what are the mostly used ones among them?
can you avoid static cache in the lookup transformation? I mean can you
How can you recognise whether or not the newly added rows in the source r gets insert in the target ?
If in a workflow i have n number of sessions and each session is configured with different different stop on errors (say 5,8,10 etc respectively).How can make all this same for all the sessions with out actualy touching each and evry session ?
How to load only the first and last record of a flat file into the target?
How are the sources and targets definitions imported in informatica designer?