2011/2/8 Emi Lu <em...@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 >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -----Original Message----- >> From: pgsql-sql-ow...@postgresql.org >> [mailto:pgsql-sql-ow...@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 > em...@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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql