Hi, Thanks again. One more question. Will crosstab function work if i will not know the number/names of columns before hand? Or I need to supply colum headings? Thanks again. NK Bruno Wolff III wrote: > On Wed, Jul 12, 2006 at 07:08:15 -0700, > nkunkov@xxxxxxxxxxxxx wrote: > > > > Thank you for the suggestions. > > I will try to describe the problem better. > > I have two problems to solve. First one is that I have to transpose a > > table. > > I have table A that looks like this: > > date product price description > > 1/1/2006 prod1 1.00 some product > > 1/1/2006 prod2 3.00 other product > > > > I need to transpose this table to create table B > > date prod1 prod2 > > 1/1/2006 1.00 3.00 > > > > I think I can use EXECUTE statement and build the table dynamically by > > using the result of the select statement for column names. Would that > > be the right approach? Are there good examples somewhere on how to > > implement this? > > The crosstabs contrib module can transpose tables for you. > > > My second problem, is that after creating the above transposed table, I > > will be inserting more rows to it from table A and i might have more > > products too. That means I will have to compare the value of product > > from table A with the column names of table B and alter the table > > accordingly. To compare coulmn names with the value of product in > > table A I think I can use pg_attribute function. Would that be a right > > way to go? > > I don't think that will work very well. I expect that adding data to the > original tables and retransposing when you need reports would be a better > way to go. > > Changing table definitions on the fly is going to be very costly and will > break concurrent access. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
