DSXchange

Hi

I have a requirement which says that I have to first pivot a table and then insert them to another table. Let us say I have two columns in the input table
ID, Value. One of the ID can have upto 80 records in the table.

I have used vertical pivot technique to flatten the values out. Now in my output seq file I have two cols which look lik this.
First column is ID and the second is values column.

10,c2,c2,c3,c4,c5
20,c11,c12
30,c1,c3,c5,c6,c9,c8

Now, How can I Insert these into a table as number of columns vary for each record ? I have never used Runtime Column Propagation. Is that the one to use here ? Is there a place I can see documentation about it ?

Describe your table. That is, what are its column names?

Table structure is :

Key,Val1,Val2,Val3,Val4,...

As of now as per the data I can see a max of 78 columns. I can achieve this by creating a table with 100 columns and then populate it but it will be an issue if the number of columns exceed 100. So I am looking for a way where it has to take the max of columns(after pivot) or max of rows(before pivot) during run time and create table based on that. We can drop and creata table everyday.

Let me know If I still did not make it clear.

Unless you have a strategy that allows a dynamic ALTER TABLE ADD COLUMN you have no solution.

You could, perhaps, pack all the remaining extra pieces into a deliberately oversized final column as a delimited string, or choose a "friendly" database such as UniVerse or UniData that allow these kinds of "hidden extra" fields.

I am going to go with one big column to hold the values. Thanks for the reply.