Обсуждение: 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 >
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?
please help.
Thanks,
-maria
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?
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
>
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/>