Re: help in writing query - Mailing list pgsql-sql

maria s escreveu:

I tried the query and it is returning result as ,
for a single entry in sample info in separate rows

The result of the query as

1, prop1,value1
1,prop2,value2
2,prop1,value1
2 prop2,value2
2 prop3,value3

but i want the output as single row per sample id like

1,value1,value2
2 value1,value2,value3

Is this possible? or functions will help to get the result?

Try tablefunc/crosstab:
http://www.postgresql.org/docs/current/interactive/tablefunc.html

please help.

Thanks,
-maria

On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:

   Hello

   SELECT i.name <http://i.name>, p.property_name, p.property_value

     FROM sample_info i
               JOIN
               sample_properties p

               ON i.id <http://i.id> = p.id <http://p.id>

   maybe
   Pavel

   2008/6/10 maria s <psmg01@gmail.com <mailto:psmg01@gmail.com>>:

    > Hello friends,
    > I need help in write a query.
    >
    > I have 2 tables, one is sample_info and sample_properties,
    >
    > sample_info (id integer, string name)
    > ------------------
    > 1, c_01
    > 2, c_02
    > ...
    >
    > sample_properties(sample_id integer(ref. sample_info),
   property_name string
    > ,property_value string )
    > -------------------------
    > 1, prop1, value1
    > 1, prop2, value2
    > 2, prop1, value1
    > 2, prop2, value 2
    > 2, prop3, value3
    >
    >
    > I would like to get the result by joining 2 tables,  for sample
   id 1 from
    > sample_info, the result should be
    >
    > 1,c_01,value1,value2
    >
    > for sample 2
    >
    > 2, c_02,value1,value2,value3
    >
    > with property_value column header as property_name
    >
    > Can anyone help me to write a query /function/view to get the
   above output?
    >

Osvaldo