write sql query following table
amount year quarter
1000 2003 first
2000 2003 second
3000 2003 third
4000 2003 fourth
5000 2004 first
6000 2004 second
7000 2004 third
8000 2004 fourth
i want the output
year q1_amount q2_amount q3_amount q4_amount
2003 1000 2000 3000 4000
2004 5000 6000 7000 8000
can anybady help me to achieve the aboue result by using informatica.
thanks in advance.
- 5 Answers
- 6274 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / hari
select * from (select year,
max(amount,quarter='first') q1_amount,
max(amount,quarter='second') q3_amount,
max(amount,quarter='third') q4_amount,
max(amount,quarter='fourth') q4_amount
from table_name group by year );
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / ankit kansal
One way of doing it is simple using aggregate function.
select year,sum(case when quarter='first' then amount end) as q1_amount
,sum(case when quarter='second' then amount end) as q2_amount
,sum(case when quarter='third' then amount end) as q3_amount
,sum(case when quarter='fourth' then amount end) as q4_amount from test group by year;
http://deepinopensource.blogspot.in/
| Is This Answer Correct ? | 1 Yes | 0 No |
Select year,
sum(case when quarter = 'first' then amount end) AS q1_amount,
sum(case when quarter = 'second' then amount end) AS q2_amount,
sum(case when quarter = 'third' then amount end) AS q3_amount,
sum(case when quarter = 'fourth' then amount end) AS q4_amount
from table
group by year order by year
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ankit kansal
on my previous answer i had given a solution using SQL query now i will give you using informatica.
1) Take Expression and create three four output ports.
(i) q1 o IIF(quater='first',amount,0)
--same for q2,q3,q4
2)In Next step use a AGG Trans.
(i) Again create four ports with group by on Year column
q1_amount sum(q1)
q2_amount sum(q2) ..
--same for q3 and q4
http://deepinopensource.blogspot.in/
| Is This Answer Correct ? | 0 Yes | 0 No |
SQL OVERRIDE :
SELECT * FROM TABLE_NAME PIVOT(MAX(AMOUNT) FOR QUARTER IN ('FIRST','SECOND','THIRD','FOURTH'));
| Is This Answer Correct ? | 0 Yes | 0 No |
What are the restrictions of union transformation?
Differentiate between mapping parameter and mapping variable?
What is a filter transformation?
Why the UNION TRANSFERMATION is an Active TRANSFERMATION
what is persistant cache in which situation we will use persistantcache
please let me know which type of question arise in interview on project manager round on insurance project with answer pls reply asap.
Explain the difference between a data warehouse and a data mart?
How we can use union transformation?
What are mapplets? How is it different from a Reusable Transformation?
when i was running the seesion, after loading 100 records, session suddenly fails.Next time i want to load the remaing rows in target with out truncate target table.What should i use?
Hi frndz, "while i am going to run the infotmatica power designer,he ask the id and password ",then i enterd the id and passwd , at that time i got the error as " failed to connect repository " ?
Diffrence between static and dynamic cache
Teradata 
