Обсуждение: "select c1, method(c2) group by c1" returns all values of c2 for c1
Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) --------------------------------- (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: ============ 1, "val1, val2, val3" 2, "val1" 3, "val5, val6" Thanks a lot! -- Lu Ying
Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu <emilu@encs.concordia.ca>: > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > --------------------------------- > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > ============ > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwards@logicworks.net http://www.linkedin.com/in/rolandoedwards -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) --------------------------------- (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: ============ 1, "val1, val2, val3" 2, "val1" 3, "val5, val6" Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
I'm not saying this is good or best but:<br /><br />select distinct a.c1, array_to_string(array(select c2 from t1 as b whereb.c1 = a.c1),',')<br /> from t1 as a;<br /> c1 | array_to_string <br />----+-----------------<br /> 1 | val1,val2,val3<br/> 2 | val1<br /> 3 | val5,val6<br />(3 rows)<br /><br /><br /><div class="gmail_quote">On Tue, Feb 8,2011 at 2:35 PM, Emi Lu <span dir="ltr"><<a href="mailto:emilu@encs.concordia.ca">emilu@encs.concordia.ca</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Good afternoon,<br /><br/> Is there a method to retrieve the following results:<br /><br /> T1(c1 int, c2 varchar(128) )<br /> ---------------------------------<br/><br /><br /> (1, val1);<br /> (1, val2);<br /> (1, val3);<br /> (2, val1);<br /> (3,val5);<br /> (3, val6);<br /><br /> select c1, method(c2)<br /> group by c1<br /><br /> returns:<br /> ============<br/> 1, "val1, val2, val3"<br /> 2, "val1"<br /> 3, "val5, val6"<br /><br /><br /> Thanks a lot!<br /><br/> --<br /> Lu Ying<br /><font color="#888888"><br /> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all" /><br/>-- <br />Peter Steinheuser<br /><a href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br />
On 02/08/2011 02:51 PM, Rolando Edwards wrote: > SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; > > Give it a Try !!! Thanks a lot! Very helpful! array_to_string() + array() is exactly what I am looking for! I just wonder that array_to_string() + array() will provide me good performance, right? If the calculation will be based on millions records. Thanks again! -- Lu Ying > Rolando A. Edwards > MySQL DBA (SCMDBA) > > 155 Avenue of the Americas, Fifth Floor > New York, NY 10013 > 212-625-5307 (Work) > 201-660-3221 (Cell) > AIM& Skype : RolandoLogicWorx > redwards@logicworks.net > http://www.linkedin.com/in/rolandoedwards > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu > Sent: Tuesday, February 08, 2011 2:36 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1 > > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > --------------------------------- > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > ============ > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 emilu@encs.concordia.ca +1 514 848-2424 x5884
2011/2/8 Emi Lu <emilu@encs.concordia.ca>: > On 02/08/2011 02:51 PM, Rolando Edwards wrote: >> >> SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where >> A.c1=B.c1),', ') from T1 A order by c1; >> >> Give it a Try !!! > > > Thanks a lot! Very helpful! > > array_to_string() + array() is exactly what I am looking for! > > I just wonder that array_to_string() + array() will provide me good > performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be a best. Regards Pavel Stehule the best speed gives a string_agg, but it is only in 9.0 > > Thanks again! > -- > Lu Ying > > > > > >> Rolando A. Edwards >> MySQL DBA (SCMDBA) >> >> 155 Avenue of the Americas, Fifth Floor >> New York, NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM& Skype : RolandoLogicWorx >> redwards@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -----Original Message----- >> From: pgsql-sql-owner@postgresql.org >> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu >> Sent: Tuesday, February 08, 2011 2:36 PM >> To: pgsql-sql@postgresql.org >> Subject: [SQL] "select c1, method(c2) group by c1" returns all values of >> c2 for c1 >> >> Good afternoon, >> >> Is there a method to retrieve the following results: >> >> T1(c1 int, c2 varchar(128) ) >> --------------------------------- >> >> >> (1, val1); >> (1, val2); >> (1, val3); >> (2, val1); >> (3, val5); >> (3, val6); >> >> select c1, method(c2) >> group by c1 >> >> returns: >> ============ >> 1, "val1, val2, val3" >> 2, "val1" >> 3, "val5, val6" >> >> >> Thanks a lot! >> >> -- >> Lu Ying >> > > > -- > Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 > emilu@encs.concordia.ca +1 514 848-2424 x5884 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >