Re: SQL Query

Hi,

Its not elegant, and certainly not dynamic or the perfect solution or for
anything but a static dataset but I've approached this in SQL before as...

First work out the maximum number of times each value of X will occur in the
table - something like "select max(subfoo.ycount) from (select foo.X,count(
foo.Y) as ycount from foo group by 1) as subfoo;" might do the job, I
haven't tested it though!

Once you have the count (lets say four, for example), you know how many
subselects you have to make...

select
superfoo.X,
coalesce((':' || (select subfoo.Y from subfoowhere subfoo.X =
superfoo.Xlimit 1)),'') ||
coalesce((':' || (select subfoo.Y from subfoowhere subfoo.X =
superfoo.Xlimit 1 offset 1)),'') ||
coalesce((':' || (select subfoo.Y from subfoowhere subfoo.X =
superfoo.Xlimit 1 offset 2)),'') ||
coalesce((':' || (select subfoo.Y from subfoowhere subfoo.X =
superfoo.Xlimit 1 offset 3)),'')
from superfoo;

Indexes would help alot also.

If anyone has any better ideas on how to do this dynamically for an unknown
count of Y values (this heads towards a pivot table) I'd love to know!

N.B. if you do coalesce((':' || subfoo.Y),'') and subfoo.Y happens to be
null, (':' || subfoo.Y) will also be null as the colon will have been wiped
out so you won't have multiple colons sitting around. Which no-one wants.

Regards,

Steve

On Dec 5, 2007 10:47 AM, Ashish Karalkar <ashish_postgre(at)yahoo(dot)co(dot)in> wrote:

>
>
> *"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>* wrote:
>
> am Wed, dem 05.12.2007, um 10:24:04 +0000 mailte Ashish Karalkar
> folgendes:
> > 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
>
> You need a new aggregate-function. A solution for a similar problem (but
> with comma instead :) can you find here:
> http://www.zigo.dhs.org/postgresql/#comma_aggregate
>
> Thanks Andreas for your replay.
> But i don't have an option two send argument to the store proc nither do i
> know how many multiple records are there for a single X. I want result for
> all rows of table.
>
> I dont thnink that function will give desired output.
>
> any suggestions?
>
> With Regards
> Ashish
>
>
>
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>
> ------------------------------
> Save all your chat conversations. Find them online.<http://in.rd.yahoo.com/tagline_webmessenger_3/*http://in.messenger.yahoo.com/webmessengerpromo.php>