Hi Thomas, No I actually need the product name (prod1, prod2....) to become column headings, which is effectively transposing the table. Thanks. NK Thomas Burdairon wrote: > 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@xxxxxxxxxxxxx wrote: > > > > > Bruno Wolff III wrote: > >> On Tue, Jul 11, 2006 at 06:05:18 -0700, > >> nkunkov@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxx 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 > > > --Apple-Mail-2-462651084 > Content-Type: text/html; charset=ISO-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 12170 > > <HTML><BODY style=3D"word-wrap: break-word; -khtml-nbsp-mode: space; = > -khtml-line-break: after-white-space; ">if i understand well you need to = > have an history for your products.<DIV>i would have a table B = > with</DIV><DIV>date =A0 =A0=A0 =A0=A0 =A0 products=A0 =A0=A0 =A0=A0 =A0=A0= > =A0price</DIV><DIV>1/1/2006=A0 =A0 =A0prod1=A0 =A0 =A0=A0 =A0=A0 =A0=A0 = > =A0=A0 =A01.0</DIV><DIV>1/1/2006=A0 =A0 =A0prod2=A0 =A0 =A0=A0 =A0=A0 =A0=A0= > =A0=A0 =A03.0</DIV><DIV><BR = > class=3D"khtml-block-placeholder"></DIV><DIV>or replace prod_name py = > product_id, ...<BR><DIV> <P style=3D"margin: 0.0px 0.0px 0.0px 0.0px; = > font: 12.0px Helvetica; min-height: 14.0px"><BR></P> <P style=3D"margin: = > 0.0px 0.0px 0.0px 0.0px"><FONT face=3D"Helvetica" size=3D"3" = > style=3D"font: 12.0px Helvetica">Thomas</FONT></P> = > </DIV><BR><DIV><DIV>On Jul 12, 2006, at 16:08, <A = > href=3D"mailto:nkunkov@xxxxxxxxxxxxx";>nkunkov@xxxxxxxxxxxxx</A> = > wrote:</DIV><BR class=3D"Apple-interchange-newline"><BLOCKQUOTE = > type=3D"cite"><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">Bruno Wolff III wrote:</DIV> <BLOCKQUOTE = > type=3D"cite"><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">On Tue, Jul 11, 2006 at 06:05:18 = > -0700,</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; "><SPAN = > class=3D"Apple-converted-space">=A0 </SPAN><A = > href=3D"mailto:nkunkov@xxxxxxxxxxxxx";>nkunkov@xxxxxxxxxxxxx</A> = > wrote:</DIV> <BLOCKQUOTE type=3D"cite"><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; = > ">Hello,</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">I'm a pgsql novice and here is = > what I'm trying to do:</DIV><DIV style=3D"margin-top: 0px; margin-right: = > 0px; margin-bottom: 0px; margin-left: 0px; ">1.<SPAN = > class=3D"Apple-converted-space">=A0 =A0 </SPAN>I need to create a = > dynamic table with the column names fetched</DIV><DIV style=3D"margin-top:= > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from = > the database using a select statement from some other table.<SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>Is</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">it possible?<SPAN class=3D"Apple-converted-space">=A0 = > </SPAN>Could you point me to a simple example on how to do it?</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">2. <SPAN class=3D"Apple-converted-space">=A0 = > </SPAN>I would like to compare the list of coulmn names which are = > values</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">fetched from some table with the = > column names of the existing table.</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">If one of the = > names doesn't exist as a column name of my table, I'd</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">like to dynamically alter the table and add a coulmn = > with the name just</DIV><DIV style=3D"margin-top: 0px; margin-right: = > 0px; margin-bottom: 0px; margin-left: 0px; ">fetched from the = > DB.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">Your help is greatly = > appreciated.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">Thanks</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">NK</DIV> </BLOCKQUOTE><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: = > 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">Information on the column names = > of tables in the database are available</DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from the = > information schema and the catlog tables. You can find more about = > this</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">in the documentation:</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; "><A = > href=3D"http://www.postgresql.org/docs/8.1/static/information-schema.html"= > >http://www.postgresql.org/docs/8.1/static/information-schema.html</A></DI= > V><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; "><A = > href=3D"http://www.postgresql.org/docs/8.1/static/catalogs.html";>http://ww= > w.postgresql.org/docs/8.1/static/catalogs.html</A></DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">You = > might get better help by describing the actual problem you are trying = > to</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: = > 0px; margin-left: 0px; ">solve rather than asking for help with a = > particular approach to solving that</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">problem. The = > approach you are trying seems to be seriously broken and it</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">would probably be a good idea to consider other = > approaches.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">---------------------------(end of = > broadcast)---------------------------</DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">TIP 1: = > if posting/reading through Usenet, please send an appropriate</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; "><SPAN class=3D"Apple-converted-space">=A0=A0 =A0 =A0 = > </SPAN>subscribe-nomail command to <A = > href=3D"mailto:majordomo@xxxxxxxxxxxxxx";>majordomo@xxxxxxxxxxxxxx</A> so = > that your</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; "><SPAN = > class=3D"Apple-converted-space">=A0=A0 =A0 =A0 </SPAN>message can get = > through to the mailing list cleanly</DIV> </BLOCKQUOTE><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Thank = > you for the suggestions.</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I will try to = > describe the problem better.</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I have two = > problems to solve.<SPAN class=3D"Apple-converted-space">=A0 </SPAN>First = > one is that I have to transpose a</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; = > ">table.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">I have table A that looks like = > this:</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">date<SPAN = > class=3D"Apple-converted-space">=A0 =A0 =A0 =A0 =A0 </SPAN>product<SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>price description</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">1/1/2006 <SPAN class=3D"Apple-converted-space">=A0 = > </SPAN>prod1<SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 = > </SPAN>1.00<SPAN class=3D"Apple-converted-space">=A0 </SPAN>some = > product</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">1/1/2006 <SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>prod2<SPAN = > class=3D"Apple-converted-space">=A0 =A0 =A0 </SPAN>3.00<SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>other product</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I need = > to transpose this table to create table B</DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">date = > <SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 =A0 </SPAN>prod1<SPAN = > class=3D"Apple-converted-space">=A0 =A0 </SPAN>prod2</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">1/1/2006 <SPAN class=3D"Apple-converted-space">=A0 = > </SPAN>1.00<SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 = > </SPAN>3.00</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">I think I can use EXECUTE statement and build the = > table dynamically by</DIV><DIV style=3D"margin-top: 0px; margin-right: = > 0px; margin-bottom: 0px; margin-left: 0px; ">using the result of the = > select statement for column names. Would that</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">be the right approach?<SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>Are there good examples = > somewhere on how to</DIV><DIV style=3D"margin-top: 0px; margin-right: = > 0px; margin-bottom: 0px; margin-left: 0px; ">implement this?</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">My = > second problem, is that after creating the above transposed table, = > I</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: = > 0px; margin-left: 0px; ">will be inserting more rows to it from table A = > and i might have more</DIV><DIV style=3D"margin-top: 0px; margin-right: = > 0px; margin-bottom: 0px; margin-left: 0px; ">products too.<SPAN = > class=3D"Apple-converted-space">=A0 </SPAN>That means I will have to = > compare the value of product</DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from table A = > with the column names of table B and alter the table</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">accordingly.<SPAN class=3D"Apple-converted-space">=A0 = > </SPAN>To compare coulmn names with the value of product in</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">table A I think I can use pg_attribute = > function.<SPAN class=3D"Apple-converted-space">=A0 </SPAN>Would that be = > a right</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">way to go?</DIV><DIV = > style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Thanks = > for your help.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">NK</DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; = > min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; = > margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: = > 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = > margin-bottom: 0px; margin-left: 0px; ">---------------------------(end = > of broadcast)---------------------------</DIV><DIV style=3D"margin-top: = > 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">TIP 6: = > explain analyze is your friend</DIV> = > </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>= > > --Apple-Mail-2-462651084--
