Hi Thomas,
No I actually need the product name (prod1, prod2....) to become column headings, which is effectively transposing the table.
Thanks.
NK
----- Original Message -----
From: Thomas Burdairon <tburdairon(at)entelience(dot)com>
Date: Wednesday, July 12, 2006 10:53 am
Subject: Re: [GENERAL] Dynamic table with variable number of columns
> if i understand well you need to have an history for your products.
> i would have a table B with
> date products price
> 1/1/2006 prod1 1.0
> 1/1/2006 prod2 3.0
>
> or replace prod_name py product_id, ...
>
> Thomas
>
> On Jul 12, 2006, at 16:08, nkunkov(at)optonline(dot)net wrote:
>
> >
> > Bruno Wolff III wrote:
> >> On Tue, Jul 11, 2006 at 06:05:18 -0700,
> >> nkunkov(at)optonline(dot)net wrote:
> >>> Hello,
> >>> I'm a pgsql novice and here is what I'm trying to do:
> >>> 1. I need to create a dynamic table with the column names
> fetched>>> from the database using a select statement from some
> other
> >>> table. Is
> >>> it possible? Could you point me to a simple example on how to
> do
> >>> it?
> >>> 2. I would like to compare the list of coulmn names which
> are
> >>> values
> >>> fetched from some table with the column names of the existing
> table.>>> If one of the names doesn't exist as a column name of my
> table, I'd
> >>> like to dynamically alter the table and add a coulmn with the
> >>> name just
> >>> fetched from the DB.
> >>> Your help is greatly appreciated.
> >>> Thanks
> >>> NK
> >>
> >> Information on the column names of tables in the database are
> >> available
> >> from the information schema and the catlog tables. You can find
>
> >> more about this
> >> in the documentation:
> >> http://www.postgresql.org/docs/8.1/static/information-schema.html
> >> http://www.postgresql.org/docs/8.1/static/catalogs.html
> >>
> >> You might get better help by describing the actual problem you
> are
> >> trying to
> >> solve rather than asking for help with a particular approach to
>
> >> solving that
> >> problem. The approach you are trying seems to be seriously
> broken
> >> and it
> >> would probably be a good idea to consider other approaches.
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 1: if posting/reading through Usenet, please send an
> appropriate>> subscribe-nomail command to
> majordomo(at)postgresql(dot)org so
> >> that your
> >> message can get through to the mailing list cleanly
> >
> > 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?
> >
> > 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?
> >
> > Thanks for your help.
> > NK
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
>