How do to add a line for each value in the Column
Moderators: chulett, rschirm, roy
How do to add a line for each value in the Column
Hello:
How to add a line for each value in the Column?
Example:
Column value in COL_A is CH, SE, SR, etc,
So for COL_A , there will be 3 rows.
How to add a line for each value in the Column?
Example:
Column value in COL_A is CH, SE, SR, etc,
So for COL_A , there will be 3 rows.
Thanks in advance,
Use the CONVERT() function and convert all commas with char(010) (new line character on unix).
This will be sufficient if there is only one column in your source. If you have multiple columns, then you can use the pivot stage.
This will be sufficient if there is only one column in your source. If you have multiple columns, then you can use the pivot stage.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks DSguru2B for the quick response. I will go ahead and try it.DSguru2B wrote:Use the CONVERT() function and convert all commas with char(010) (new line character on unix).
This will be sufficient if there is only one column in your source. If you have multiple columns, then you can use the pivot stage.
Thanks in advance,
Hi DSguru2B
This is the first time I am using a Pivot Stage.
I have multiple columns on the source file. A Horizontal Pivot would create columns within a single row into many rows)
My Source file looks like this:
Under COL_A I have values AC, AG, MA, MM, PH, RE, SR, VO and there should only be one value in this column, so there should be 8 rows here, one for each value and not just one.
SO My Target should populate Like below:
Can I achieve this with using Pivot stage...?
This is the first time I am using a Pivot Stage.
I have multiple columns on the source file. A Horizontal Pivot would create columns within a single row into many rows)
My Source file looks like this:
Code: Select all
COL_A | COL_B | COL_C | and so on
AC, AG, MA, MM, PH, RE, SR, VO | 1 | ZA0 | ......
MH, BO,NA | 2 | ZZZ |
SO My Target should populate Like below:
Code: Select all
COL_A | COL_B | COL_C | and so on
AC | 1 | ZA0 | ......
AG | 1 | ZA0 |
MA | 1 | ZA0 |
MM | 1 | ZA0 | ......
AG | 1 | ZA0 |
PH | 1 | ZA0 | ......
AG | 1 | ZA0 |
RE | 1 | ZA0 | ......
AG | 1 | ZA0 |
SR | 1 | ZA0 | ......
AG | 1 | ZA0 |
VO | 1 | ZA0 | ......
AG | 1 | ZA0 |
MH | 2 | ZZZ |
BO | 2 | ZZZ |
NA | 2 | ZZZ |
Thanks in advance,
Hello DSguru2B:
I am using DS 7.5.2.
In the Help ---under Px Job Stages ---> Processing Stages I dont see the Pivot stage I see all the other stages, nor is it underRestructure stage. When I do a find it just gives the stage its Input and output pages and explanation about it. There is no example. Is there somewhere else I can get this..?
I am using DS 7.5.2.
In the Help ---under Px Job Stages ---> Processing Stages I dont see the Pivot stage I see all the other stages, nor is it underRestructure stage. When I do a find it just gives the stage its Input and output pages and explanation about it. There is no example. Is there somewhere else I can get this..?
Thanks in advance,
GO to the Online documentation. Hit start on your desktop, go to Programs, find Ascential DataStage and go to Online Manuals.
Click on DataStage Documentation, then go to Plugin Stages. Once inside, search for Pivot Stage and read its manual.
You can also search here about pivot stage and real time examples that dsxians faced.
Click on DataStage Documentation, then go to Plugin Stages. Once inside, search for Pivot Stage and read its manual.
You can also search here about pivot stage and real time examples that dsxians faced.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi DSguru2B:
Thanks I did find Pivot stage in the Plugins. The example showing in there has a difference. In the example it has different Columns that need pivoting. So it has columns Jan, Feb,Mar that are pivoted.
However, my job in 1 column (COL_A) it has data seperated by a comma and this seperated data from just COL_A needs to be pivoted.
I used the pivot stage and was able to pivot columns. But mine is
value in a column which is seperated by a comma that needs pivoting and am unable to do it.
So I was able to pivot some columns but i was unable to pivot the data in COL_A which is seperated by a comma.
Thanks I did find Pivot stage in the Plugins. The example showing in there has a difference. In the example it has different Columns that need pivoting. So it has columns Jan, Feb,Mar that are pivoted.
However, my job in 1 column (COL_A) it has data seperated by a comma and this seperated data from just COL_A needs to be pivoted.
I used the pivot stage and was able to pivot columns. But mine is
value in a column which is seperated by a comma that needs pivoting and am unable to do it.
So I was able to pivot some columns but i was unable to pivot the data in COL_A which is seperated by a comma.
Thanks in advance,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: