SOURCE TARGET
id name id name1 name2 name3
1 a 1 a b c
1 b 2 x y null
1 c
2 x
2 y
Try this
Informatica Solution:
SQ --------> SRT ------> EXP ---------> AGG-------->TGT
Step1: Use sorter transformation and sort the data using id port as the key. Then connect the sorter transformation to the expression transformation.
Step2: In the expression transformation, create the ports and assign the expressions as mentioned below.
id
name
V_curr_id=id
V_count= IIF(v_curr_id=V_prev_id,V_count 1,1)
V_prev_id=id
O_col1= IIF(V_count=1,name,NULL)
O_col2= IIF(V_count=2,name,NULL)
O_col3= IIF(V_count=3,name,NULL)
Step3: Connect the expression transformation to aggregator transformation. In the aggregator transforamtion, create the ports and assign the expressions as mentioned below.
id (specify group by on this port)
O_col1
O_col2
O_col3
col1=MAX(O_col1)
col2=MAX(O_col2)
col3=MAX(O_col3)
Stpe4: Now connect the ports id, col1, col2, col3 from aggregator transformation to the target table.
Oracle solution:
SELECT id,
max(decode(rank_id,1,loc)) loc1,
max(decode(rank_id,2,loc)) loc2,
max(decode(rank_id,3,loc)) loc3,
from (select id,name,rank()over (partition by id order by id,name) rank_id from table_name) group by id;
COL to ROWS
We have the following records in the table:
SQL> SELECT *
2 FROM survey
3 /
SURVEY_ID CONCERN_EYE CONCERN_VISION CONCERN_BRAIN CONCERN_LUNG CONCERN_HIP
--------------------------------------- ----------- -------------- ------------- ------------ -----------
123 0 0 1 0 1
321 1 1 0 0 0
456 0 0 0 0 0
654 1 1 0 1 1
789 0 1 0 0 0
987 0 0 0 0 1
6 rows selected
For one of our company’s reports, we need to come up with a SQL query where the columns are represented as rows – meaning that we have transposed the columns to rows.
Example # 1: Using the UNION operator
In this sample, we will solve the task simply by using several UNION statements.
Our end result should be a resultset with 30 records. The reason for this is that
we have six (6) unique ids, and we have five (5) columns for each of the ids. And… 6 x 5 = 30
SQL> WITH survey_query AS (
2 SELECT *
3 FROM survey s
4 )
5 SELECT sq.survey_id
6 ,'EYE' concern_type
7 ,sq.concern_eye concern_flag
8 FROM survey_query sq
9 UNION
10 SELECT sq.survey_id
11 ,'VISION' concern_type
12 ,sq.concern_vision concern_flag
13 FROM survey_query sq
14 UNION
15 SELECT sq.survey_id
16 ,'BRAIN' concern_type
17 ,sq.concern_brain concern_flag
18 FROM survey_query sq
19 UNION
20 SELECT sq.survey_id
21 ,'LUNG' concern_type
22 ,sq.concern_lung concern_flag
23 FROM survey_query sq
24 UNION
25 SELECT sq.survey_id
26 ,'HIP' concern_type
27 ,sq.concern_hip concern_flag
28 FROM survey_query sq
29 /
SURVEY_ID CONCERN_TYPE CONCERN_FLAG
--------------------------------------- ------------ ------------
123 BRAIN 1
123 EYE 0
123 HIP 1
123 LUNG 0
123 VISION 0
321 BRAIN 0
321 EYE 1
321 HIP 0
321 LUNG 0
321 VISION 1
456 BRAIN 0
456 EYE 0
456 HIP 0
456 LUNG 0
456 VISION 0
654 BRAIN 0
654 EYE 1
654 HIP 1
654 LUNG 1
654 VISION 1
789 BRAIN 0
789 EYE 0
789 HIP 0
789 LUNG 0
789 VISION 1
987 BRAIN 0
987 EYE 0
987 HIP 1
987 LUNG 0
987 VISION 0
30 rows selected
As you can see, we got our 30 records. Also, note that I used a WITH statement for my main query.
This is because I want to make sure that my query does not pull data from the survey table five times, but only once.
Note: Based on your data, you might want to consider using UNION ALL rather than UNION,
whatever gives you the correct result.
Example # 2: Using the UNPIVOT operator
If you have a database with Oracle database version 11g or later, you might consider using the UNPIVOT operator to solve your task.
Here is a sample:
SQL> SELECT survey_id
2 ,concern_type
3 ,concern_flag
4 FROM survey
5 UNPIVOT ( concern_flag FOR concern_type IN (
6 concern_eye AS 'EYE'
7 ,concern_vision AS 'VISION'
8 ,concern_brain AS 'BRAIN'
9 ,concern_lung AS 'LUNG'
10 ,concern_hip AS 'HIP')
11 )
12 /
SURVEY_ID CONCERN_TYPE CONCERN_FLAG
--------------------------------------- ------------ ------------
123 EYE 0
123 VISION 0
123 BRAIN 1
123 LUNG 0
123 HIP 1
321 EYE 1
321 VISION 1
321 BRAIN 0
321 LUNG 0
321 HIP 0
456 EYE 0
456 VISION 0
456 BRAIN 0
456 LUNG 0
456 HIP 0
654 EYE 1
654 VISION 1
654 BRAIN 0
654 LUNG 1
654 HIP 1
789 EYE 0
789 VISION 1
789 BRAIN 0
789 LUNG 0
789 HIP 0
987 EYE 0
987 VISION 0
987 BRAIN 0
987 LUNG 0
987 HIP 1
30 rows selected
As you can see, we have the same amount of records.
In the query above, the “concern_type” and the “concern_flag” fields can be named anything.
NOTE: If you choose to use the UNPIVOT operator – you need to be aware that if you are using an alias on your table,
you might getting error messages. A query using UNPIVOT does not seem to like aliases very well.
Also, in the above query, you see that I use i.e. concern_eye AS ‘EYE’. This is a way that the aliasing works, so try to be careful.
Conclusion
Ok, so we have seen two different ways to transpose columns into rows in Oracle, using SQL.
The first example might be a good way, but it requires a little bit more code.
The second example, using UNPIVOT, is a good way to go if your database version is 11g or newer.
There are though a few things to be aware of using the UNPIVOT operator, especially when using aliases.
id name id name1 name2 name3
1 a 1 a b c
1 b 2 x y null
1 c
2 x
2 y
Try this
Informatica Solution:
SQ --------> SRT ------> EXP ---------> AGG-------->TGT
Step1: Use sorter transformation and sort the data using id port as the key. Then connect the sorter transformation to the expression transformation.
Step2: In the expression transformation, create the ports and assign the expressions as mentioned below.
id
name
V_curr_id=id
V_count= IIF(v_curr_id=V_prev_id,V_count 1,1)
V_prev_id=id
O_col1= IIF(V_count=1,name,NULL)
O_col2= IIF(V_count=2,name,NULL)
O_col3= IIF(V_count=3,name,NULL)
Step3: Connect the expression transformation to aggregator transformation. In the aggregator transforamtion, create the ports and assign the expressions as mentioned below.
id (specify group by on this port)
O_col1
O_col2
O_col3
col1=MAX(O_col1)
col2=MAX(O_col2)
col3=MAX(O_col3)
Stpe4: Now connect the ports id, col1, col2, col3 from aggregator transformation to the target table.
Oracle solution:
SELECT id,
max(decode(rank_id,1,loc)) loc1,
max(decode(rank_id,2,loc)) loc2,
max(decode(rank_id,3,loc)) loc3,
from (select id,name,rank()over (partition by id order by id,name) rank_id from table_name) group by id;
COL to ROWS
We have the following records in the table:
SQL> SELECT *
2 FROM survey
3 /
SURVEY_ID CONCERN_EYE CONCERN_VISION CONCERN_BRAIN CONCERN_LUNG CONCERN_HIP
--------------------------------------- ----------- -------------- ------------- ------------ -----------
123 0 0 1 0 1
321 1 1 0 0 0
456 0 0 0 0 0
654 1 1 0 1 1
789 0 1 0 0 0
987 0 0 0 0 1
6 rows selected
For one of our company’s reports, we need to come up with a SQL query where the columns are represented as rows – meaning that we have transposed the columns to rows.
Example # 1: Using the UNION operator
In this sample, we will solve the task simply by using several UNION statements.
Our end result should be a resultset with 30 records. The reason for this is that
we have six (6) unique ids, and we have five (5) columns for each of the ids. And… 6 x 5 = 30
SQL> WITH survey_query AS (
2 SELECT *
3 FROM survey s
4 )
5 SELECT sq.survey_id
6 ,'EYE' concern_type
7 ,sq.concern_eye concern_flag
8 FROM survey_query sq
9 UNION
10 SELECT sq.survey_id
11 ,'VISION' concern_type
12 ,sq.concern_vision concern_flag
13 FROM survey_query sq
14 UNION
15 SELECT sq.survey_id
16 ,'BRAIN' concern_type
17 ,sq.concern_brain concern_flag
18 FROM survey_query sq
19 UNION
20 SELECT sq.survey_id
21 ,'LUNG' concern_type
22 ,sq.concern_lung concern_flag
23 FROM survey_query sq
24 UNION
25 SELECT sq.survey_id
26 ,'HIP' concern_type
27 ,sq.concern_hip concern_flag
28 FROM survey_query sq
29 /
SURVEY_ID CONCERN_TYPE CONCERN_FLAG
--------------------------------------- ------------ ------------
123 BRAIN 1
123 EYE 0
123 HIP 1
123 LUNG 0
123 VISION 0
321 BRAIN 0
321 EYE 1
321 HIP 0
321 LUNG 0
321 VISION 1
456 BRAIN 0
456 EYE 0
456 HIP 0
456 LUNG 0
456 VISION 0
654 BRAIN 0
654 EYE 1
654 HIP 1
654 LUNG 1
654 VISION 1
789 BRAIN 0
789 EYE 0
789 HIP 0
789 LUNG 0
789 VISION 1
987 BRAIN 0
987 EYE 0
987 HIP 1
987 LUNG 0
987 VISION 0
30 rows selected
As you can see, we got our 30 records. Also, note that I used a WITH statement for my main query.
This is because I want to make sure that my query does not pull data from the survey table five times, but only once.
Note: Based on your data, you might want to consider using UNION ALL rather than UNION,
whatever gives you the correct result.
Example # 2: Using the UNPIVOT operator
If you have a database with Oracle database version 11g or later, you might consider using the UNPIVOT operator to solve your task.
Here is a sample:
SQL> SELECT survey_id
2 ,concern_type
3 ,concern_flag
4 FROM survey
5 UNPIVOT ( concern_flag FOR concern_type IN (
6 concern_eye AS 'EYE'
7 ,concern_vision AS 'VISION'
8 ,concern_brain AS 'BRAIN'
9 ,concern_lung AS 'LUNG'
10 ,concern_hip AS 'HIP')
11 )
12 /
SURVEY_ID CONCERN_TYPE CONCERN_FLAG
--------------------------------------- ------------ ------------
123 EYE 0
123 VISION 0
123 BRAIN 1
123 LUNG 0
123 HIP 1
321 EYE 1
321 VISION 1
321 BRAIN 0
321 LUNG 0
321 HIP 0
456 EYE 0
456 VISION 0
456 BRAIN 0
456 LUNG 0
456 HIP 0
654 EYE 1
654 VISION 1
654 BRAIN 0
654 LUNG 1
654 HIP 1
789 EYE 0
789 VISION 1
789 BRAIN 0
789 LUNG 0
789 HIP 0
987 EYE 0
987 VISION 0
987 BRAIN 0
987 LUNG 0
987 HIP 1
30 rows selected
As you can see, we have the same amount of records.
In the query above, the “concern_type” and the “concern_flag” fields can be named anything.
NOTE: If you choose to use the UNPIVOT operator – you need to be aware that if you are using an alias on your table,
you might getting error messages. A query using UNPIVOT does not seem to like aliases very well.
Also, in the above query, you see that I use i.e. concern_eye AS ‘EYE’. This is a way that the aliasing works, so try to be careful.
Conclusion
Ok, so we have seen two different ways to transpose columns into rows in Oracle, using SQL.
The first example might be a good way, but it requires a little bit more code.
The second example, using UNPIVOT, is a good way to go if your database version is 11g or newer.
There are though a few things to be aware of using the UNPIVOT operator, especially when using aliases.
Source: http://oraclecoder.com/tutorials/how-to-transpose-columns-to-rows-in-oracle--2435
Through Informatica:
Using normalizer transformation or union transformation if you have fixed set of columns
For dynamic you have to use java transformation:
Below is the example for Union
Convert column into row without using Normalizer Transformation
Source is
ID YEAR1 YEAR2 YEAR3
1 200 300 400
2 500 600 700
Output
ID YEAR
1 200
1 300
1 400
2 500
2 600
2 700
Achieve the result without using Normalizer Transformation.
Solution:
1. Bring the source in to the mapping designer.
2. Create three expression transformation.
3. Drag ID and YEAR1 ports from the source qualifier into the first expression.
4. Drag ID and YEAR2 ports from the source qualifier into the second expression
5. Drag ID and YEAR3 ports from the source qualifier into the third expression.
6. Take a UNION transformation and create three ports.
7. Drag ports from the first expression transformation into the first group of union transformation.
8. Drag ports from the second expression transformation into the second group of union transformation.
9. Drag ports from the third expression transformation into the third group of union transformation.
10. Take a sorter transformation and sort on id.
11. Now connect to the target.
No comments:
Post a Comment