Hi all,
I am loading an XML file which contains about 10,000 records.
I am using DS 7.5.1A PX jobs on UNIX platform.
These XML files are to be loaded in the SQL Server DB.
After parsing the XML file using DS job got the following output Dataset.
Ex:
Seq_ID, Field_Name, Value
1, Gender, Male
1, Age, 29
1, Nativity, Local
2, Gender, Female
2, Age, 25
2, Nativity, Non-Local
But I need to convert the above data into the following format.
Seq_ID, Gender, Age, Nativity
1, Male, 29, Local
2, Female, 25, Non-Local
Note: I had mentioned only three field names for the header in the above example. may be in future the count of field names could be dynamically change like Gender, Age, Nativity, Religion........, but the output table structure is constant in the DB.
Can anybody please help me out is there any way to resolve the issue.
ThanX,
Vijetha
How to convert form Rows into Columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard. This technique is called Vertical Pivot.
Search the forum for techniques - the main one uses Transformer stage with stage variables.
In a future release (8.2?) the Pivot stage will handle vertical pivot.
Search the forum for techniques - the main one uses Transformer stage with stage variables.
In a future release (8.2?) the Pivot stage will handle vertical pivot.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 5
- Joined: Sat Feb 27, 2010 12:21 am
- Location: vinukonda
Re: How to convert form Rows into Columns
hi
hear i am sending the answer , which is useful for u
1)apply stagevar logic
2)after that , by using the remove duplicate ,take first row
3)after that , by using fied function in transformer
4)collect the data to output
i am giving the hint
try it
if u does not get , i wil send the full logic ,how to implement
ok
hear i am sending the answer , which is useful for u
1)apply stagevar logic
2)after that , by using the remove duplicate ,take first row
3)after that , by using fied function in transformer
4)collect the data to output
i am giving the hint
try it
if u does not get , i wil send the full logic ,how to implement
ok
venkat
In the intervening year I would wager they've either solved this problem or given up and moved on to another.
ps. I doubt it is useful for "u" as our dear friend "U" wasn't asking the question. If you actually meant "you" next time it would be great if you actually included the two extra letters that apparently were too much trouble for you to type this time. We're not on cell phones here. Thanks!
ps. I doubt it is useful for "u" as our dear friend "U" wasn't asking the question. If you actually meant "you" next time it would be great if you actually included the two extra letters that apparently were too much trouble for you to type this time. We're not on cell phones here. Thanks!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Actually this would be a little more difficult that a vertical pivot. As the xml can contain a variable number of columns which will actually be loaded into a standardised table.
A vertical pivot will only work (in the standard sense) if all the values come in the same order each time, where as here, they most likely will not.
Applying this would require you to generate the incoming values into a fixed format, or pairing the column name and sequence number to the target dataset.
Does anybody know if its possible to dynamically generate an sql statement to update based on a key value and setting the field to update on the fly?
A vertical pivot will only work (in the standard sense) if all the values come in the same order each time, where as here, they most likely will not.
Applying this would require you to generate the incoming values into a fixed format, or pairing the column name and sequence number to the target dataset.
Does anybody know if its possible to dynamically generate an sql statement to update based on a key value and setting the field to update on the fly?