How to convert form Rows into Columns

[vijetha]

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

[DSguru2B]

Search here for Vertical Pivoting. That should head you in the right direction.

[ray.wurlod]

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.

[venkatgajavalli1]

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

[chulett]

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!

[ShaneMuir]

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?