Another PIVOT/TRANSPOSE question
Posted: Wed May 14, 2008 10:04 am
I have an XML file that needs to be transposed:
Source:
Same Date, Unknown number of Group_Type, Group_Num, approx 2280 records per as shown below. There will always be 13 "stat" columns that will have values or be null (Field_Name is a character field):
Our DB2 table, however, wants the layout as follows, where the column name is the value of the "Field_Name" and the Value of the derived field
Stat_Typ is the name(s) of the statistic columns, and would be a character field:
So for a given set of date, group type, group number, 2280 rows x 17 columns would become 17 rows by 2284 columns (2280 fields and 4 key columns). Nothing is to be summed, averaged and so on. It is a pure transposition of data. I have looked thru some of the other posts on vertical pivots and such and can't seem to find an answer. I realize there may be performance issues as well.
Can an aggregrator stage do this? The pivot stage always seems to create even more columns, so I don't think this is an option.
Source:
Same Date, Unknown number of Group_Type, Group_Num, approx 2280 records per as shown below. There will always be 13 "stat" columns that will have values or be null (Field_Name is a character field):
Code: Select all
Date Group_Type Group_Num Field_Name Stat1 Stat2 .. Stat13
9/07 A 1 Field1 100 350 9999
9/07 A 1 Field2 200 500 8888
.
.
.
9/07 A 1 Field2280 45 6000 19999
9/07 A 2 Field1 .............................
Stat_Typ is the name(s) of the statistic columns, and would be a character field:
Code: Select all
Date Group_Type Group_Num Stat_Type Field1 Field2 ... Field2280
9/07 A 1 Stat1 100 200 45
9/07 A 1 Stat2 350 500 6000
9/07 A 1 Stat13 9999 8888 19999
Can an aggregrator stage do this? The pivot stage always seems to create even more columns, so I don't think this is an option.