Table with varying number of columns...

kaps · Post by **kaps** » Mon Feb 28, 2011 4:50 pm

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 ?

ray.wurlod · Post by **ray.wurlod** » Mon Feb 28, 2011 9:07 pm

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

kaps · Post by **kaps** » Mon Feb 28, 2011 11:33 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 28, 2011 11:45 pm

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.

kaps · Post by **kaps** » Tue Mar 01, 2011 5:15 pm

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