Обсуждение: Re: help in writing query

Hello

SELECT i.name, p.property_name, p.property_value  FROM sample_info i            JOIN            sample_properties p
      ON i.id = p.id
 

maybe
Pavel

2008/6/10 maria s <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?
>
> Thank you so much for your help.
>
> -maria
>

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

2008/6/10 maria s <psmg01@gmail.com>:
> Hi Pavel,
> Thank you for your reply.
>
> 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?

I am not sure. Optimal query depend on your postgresql version and
expected size of result set.

I don't see property name in you result?

Pavel

>
> please help.
>
> Thanks,
> -maria
>
> On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> SELECT i.name, p.property_name, p.property_value
>>   FROM sample_info i
>>             JOIN
>>             sample_properties p
>>             ON i.id = p.id
>>
>> maybe
>> Pavel
>>
>> 2008/6/10 maria s <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?
>> >
>> > Thank you so much for your help.
>> >
>> > -maria
>> >
>
>

On Tue, Jun 10, 2008 at 11:51 AM, maria s <psmg01@gmail.com> wrote:
> Hi Rosario,
> Thanks for the link. I hope this will solve my problem.

It should be able to.  Note that crosstab functions expect "square"
inputs from the select they run.  I.e. you can't have empty columns,
you need to replace NULL output with something like a space or empty
string.

This is bad input for crosstab:

col1 col2 col3
1 2 3
2 3 NULL
3 NULL 6

But this will work:

col1 col2 col3
1 2 3
2 3 '' <- an empty string
3 '' 6

The crosstab functions are wonderfully useful btw, once you figure all
the little quirks like this out.