Skip site navigation (1) Skip section navigation (2)

Re: help in writing query

From: Osvaldo Rosario Kussama <osvaldo(dot)kussama(at)gmail(dot)com>
To: maria s <psmg01(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: help in writing query
Date: 2008-06-10 15:34:05
Message-ID: 484E9EED.8040307@gmail.com (view raw or flat)
Thread:
Lists: 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(dot)stehule(at)gmail(dot)com 
> <mailto:pavel(dot)stehule(at)gmail(dot)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(at)gmail(dot)com <mailto:psmg01(at)gmail(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Pavel StehuleDate: 2008-06-10 15:50:16
Subject: Re: help in writing query
Previous:From: samantha mahindrakarDate: 2008-06-10 15:22:43
Subject: Re: Trouble with exception

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group