Converting Rows to columns
I have Relational source like his.
JAN FEB MAR APR
100 200 300 400
500 600 700 800
900 100 200 300
I need to convert these rows into columns to the targe.
MONTH TOTAL
JAN 1500
FEB 900
MAR 1200
APR 1500
Please experts help me
- 8 Answers
- 8841 Views
- HP, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / sehajshangari1235
TAKE A NORMALIZER TRANSFORMATION.
CREATE A NORMALIZED PORT NAMED "DETAIL" WITH OCCURENCE 4
.CONNECT INPUT PORTS FROM SOURCE QUALIFIER TO EACH DETAIL
PORT IN NORMALIZER.
NEXT TAKE AN EXPRESSION TRANSFORMATION.IN THAT CREATE AN
OUTPUT PORT NAMED MONTH.AN IN EXPRESSION EDITER WRITE THE
LOGIC AS
DECODE(GCID_DETAIL,1,'JAN',DECODE(GCID_DETAIL,2,'FEB',DECODE(
GCID_DETAIL,3,'MARCH','APRIL')))
CONNECT THIS OUTPUT PORT TO TARGET TABLE PORT!!!
SEHAJ
7204719446
| Is This Answer Correct ? | 3 Yes | 1 No |
Answer / sham
we can't sum aggregate function in Exp T/F.i.e it is not
possible to use sum() in expression T/F. so go through Agg T/F
....
Group by month
o/p port-------> sum(sal)
| Is This Answer Correct ? | 3 Yes | 2 No |
Answer / reddy
Answer:
Source --> Source qualifier --> Normalizer --> Expr -->
Agg --> target
Step 1:
Source --> Source qualifier --> Normalizer --> Expr -->
Expression Condition:
DECODE(GCID_DETAIL,1,'JAN',DECODE(GCID_DETAIL,2,'FEB',DECODE
(
GCID_DETAIL,3,'MARCH','APRIL')))
JAN 100
JAN 500
JAN 900
FEB 200
FEB 600
FEB 100
MAR 300
MAR 700
MAR 200
APR 400
APR 800
ApR 300
Step 2:
Source --> Source qualifier --> Normalizer --> Expr -->
Agg --> target
1) Group by month
2) Sum(Amount)
MONTH TOTAL
JAN 1500
FEB 900
MAR 1200
APR 1500
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / prabhu
Source qualifier --> Normalizer --> Expr --> Agg --> target
In Normalizer give the four numeric values as input and then take numeric value and it's GK_value to expression.
In Expression i have made flag for month and hardcoding values of month
Flag_Jan --> IN(GK_Salary,1,5,9,0)
Flag_Feb --> in(GK_Salary,2,6,10,0)
Flag_Mar --> in(GK_Salary,3,7,11,0)
Month --> iif(Flag_Jan = 1,'Jan',iif(Flag_Feb = 1,'Feb',iif(Flag_Mar = 1,'Mar','Aprl')))
In agg group by on month and get sun(numeric value)
Pass month and sum(numeric) value to target
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / dilip ingole
using query
SELECT 'JAN' AS MONTHS,SUM(JAN) FROM CALADERQUERY
UNION
SELECT 'FEB' AS MONTHS,SUM(FEB) FROM CALADERQUERY
UNION
SELECT 'MAR' AS MONTHS,SUM(MAR) FROM CALADERQUERY
UNION
SELECT 'APR' AS MONTHS,SUM(APR) FROM CALADERQUERY;
| Is This Answer Correct ? | 0 Yes | 0 No |
You will take Expression Transfermation.
In Expression Editon you can write lke this
sum(months) and group by month.
| Is This Answer Correct ? | 2 Yes | 5 No |
i have different sources in different databases ,that sources may be 100 tables i want to load these tables in to single target how to pearform the task
When do we use dynamic cache and static cache in connected and unconnected lookup transformations?
What is the difference between view and materialised view?
If informatica has its scheduler why using third party scheduler?
What are the different types of olap? Give an example?
which one is better performance wise joiner or look up
What is Factless fact table ???
12 Answers Satyam, Tech Mahindra,
I have some rows in source.I have to map half rows to one target and half rows to another target.i.e you wont know how many records in source.?Implement a mapping?
What is the function of look up transformation?
Explain in detail scd type 2 through mapping.
IN SCD1, insource we have 10 billion records and in the first day its uploaded successfully and in the second day its taking time to upload because some records it might get update or insert new records. As a developer what will be the better solution for this??
How do you take care of security using a repository manager
Teradata 
