Re: SQL Query

On Wed, Dec 05, 2007 at 10:24:04AM +0000, Ashish Karalkar wrote:
> Hello List member,
>
> Iha a table containing two columns x and y . for single value of x there are multiple values in y e.g
>
> X        Y
> ------------
> 1        ABC
> 2        PQR
> 3         XYZ
> 4         LMN
> 1         LMN
> 2         XYZ
>
> I want a query that will give me following output
>
> 1    ABC:LMN
> 2    PQR:XYZ
> 3    XYZ
> 4    LMN
>
> Any help will be really helpful

Use the array_accum aggregate from the docs as follows:

SELECT x, array_to_string(array_accum(y),':')
FROM your_table
GROUP BY x;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate