Thread: 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
>> >
>
>

Hi Rosario,<br />Thanks for the link. I hope this will solve my problem.<br /><br />Thanks,<br />Maria<br /><br /><div
class="gmail_quote">OnTue, Jun 10, 2008 at 11:34 AM, Osvaldo Rosario Kussama <<a
href="mailto:osvaldo.kussama@gmail.com">osvaldo.kussama@gmail.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">maria s escreveu:<div
class="Ih2E3d"><br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt
0pt0.8ex; padding-left: 1ex;"><br /> I tried the query and it is returning result as ,<br /> for a single entry in
sampleinfo in separate rows<br /><br /> The result of the query as<br /><br /> 1, prop1,value1<br /> 1,prop2,value2<br
/>2,prop1,value1<br /> 2 prop2,value2<br /> 2 prop3,value3<br /><br /> but i want the output as single row per sample
idlike<br /><br /> 1,value1,value2<br /> 2 value1,value2,value3<br /><br /> Is this possible? or functions will help to
getthe result?<br /></blockquote><br /></div> Try tablefunc/crosstab:<br /><a
href="http://www.postgresql.org/docs/current/interactive/tablefunc.html"
target="_blank">http://www.postgresql.org/docs/current/interactive/tablefunc.html</a><br/><br /><br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br
/>please help.<br /><br /> Thanks,<br /> -maria<div class="Ih2E3d"><br /><br /> On Tue, Jun 10, 2008 at 11:03 AM, Pavel
Stehule<<a href="mailto:pavel.stehule@gmail.com" target="_blank">pavel.stehule@gmail.com</a> <mailto:<a
href="mailto:pavel.stehule@gmail.com"target="_blank">pavel.stehule@gmail.com</a>>> wrote:<br /><br />    Hello<br
/><br/></div>    SELECT <a href="http://i.name" target="_blank">i.name</a> <<a href="http://i.name"
target="_blank">http://i.name</a>>,p.property_name, p.property_value<div class="Ih2E3d"><br />      FROM sample_info
i<br/>                JOIN<br />                sample_properties p<br /></div>                ON <a href="http://i.id"
target="_blank">i.id</a><<a href="http://i.id" target="_blank">http://i.id</a>> = <a href="http://p.id"
target="_blank">p.id</a><<a href="http://p.id" target="_blank">http://p.id</a>><br /><br />    maybe<br />  
 Pavel<br/><br />    2008/6/10 maria s <<a href="mailto:psmg01@gmail.com" target="_blank">psmg01@gmail.com</a>
<mailto:<ahref="mailto:psmg01@gmail.com" target="_blank">psmg01@gmail.com</a>>>:<div class="Ih2E3d"><br />    
>Hello friends,<br />     > I need help in write a query.<br />     ><br />     > I have 2 tables, one is
sample_infoand sample_properties,<br />     ><br />     > sample_info (id integer, string name)<br />     >
------------------<br/>     > 1, c_01<br />     > 2, c_02<br />     > ...<br />     ><br />     >
sample_properties(sample_idinteger(ref. sample_info),<br />    property_name string<br />     > ,property_value
string)<br />     > -------------------------<br />     > 1, prop1, value1<br />     > 1, prop2, value2<br />
   > 2, prop1, value1<br />     > 2, prop2, value 2<br />     > 2, prop3, value3<br />     ><br />    
><br/>     > I would like to get the result by joining 2 tables,  for sample<br />    id 1 from<br />     >
sample_info,the result should be<br />     ><br />     > 1,c_01,value1,value2<br />     ><br />     > for
sample2<br />     ><br />     > 2, c_02,value1,value2,value3<br />     ><br />     > with property_value
columnheader as property_name<br />     ><br />     > Can anyone help me to write a query /function/view to get
the<br/>    above output?<br />     ><br /></div></blockquote><font color="#888888"><br /> Osvaldo<br
/></font></blockquote></div><br/> 

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.

Hi Scott,<br />Thanks for the information. This is very useful for me.<br />I will be careful when forming the column.
<br/><br />Thanks,<br />-maria<br /><br /><div class="gmail_quote">On Tue, Jun 10, 2008 at 3:16 PM, Scott Marlowe
<<ahref="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On
Tue,Jun 10, 2008 at 11:51 AM, maria s <<a href="mailto:psmg01@gmail.com">psmg01@gmail.com</a>> wrote:<br /> >
HiRosario,<br /> > Thanks for the link. I hope this will solve my problem.<br /><br /></div>It should be able to.
 Notethat crosstab functions expect "square"<br /> inputs from the select they run.  I.e. you can't have empty
columns,<br/> you need to replace NULL output with something like a space or empty<br /> string.<br /><br /> This is
badinput for crosstab:<br /><br /> col1 col2 col3<br /> 1 2 3<br /> 2 3 NULL<br /> 3 NULL 6<br /><br /> But this will
work:<br/><br /> col1 col2 col3<br /> 1 2 3<br /> 2 3 '' <- an empty string<br /> 3 '' 6<br /><br /> The crosstab
functionsare wonderfully useful btw, once you figure all<br /> the little quirks like this out.<br
/></blockquote></div><br/>