Re: SQL Query

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

Thanks in advance

With Regards
Ashish

---------------------------------
Why delete messages? Unlimited storage is just a click away.

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

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

"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.

am Wed, dem 05.12.2007, um 10:47:44 +0000 mailte Ashish Karalkar folgendes:
> > 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.

test=# create table Ashish ( x int, y text);
CREATE TABLE
test=*# copy ashish from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 abc
>> 2 pqr
>> 3 yxz
>> 4 lmn
>> 1 lmn
>> 2 xyz
>> \.
test=*# CREATE FUNCTION my_aggregate(text,text) RETURNS text AS ' SELECT CASE WHEN $1 <> '''' THEN $1 || '':'' || $2 ELSE $2 END; ' LANGUAGE sql IMMUTABLE STRICT;
CREATE AGGREGATE my_comma (basetype=text, sfunc=my_aggregate , stype=text, initcond='' );
CREATE FUNCTION
CREATE AGGREGATE
test=*# select x, my_comma(y) from ashish group by x;
x | my_comma
---+----------
4 | lmn
3 | yxz
2 | pqr:xyz
1 | abc:lmn
(4 rows)

Okay, i forgot to sort and the chars are in lower case...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

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>

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

am Wed, dem 05.12.2007, um 3:46:26 -0800 mailte David Fetter folgendes:
> Use the array_accum aggregate from the docs as follows:
>
> SELECT x, array_to_string(array_accum(y),':')
> FROM your_table
> GROUP BY x;

Yes, no noubt a better solution as my new aggregat...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Wed, Dec 05, 2007 at 11:43:08AM +0000,
Steve Grey <stevegrey78(at)gmail(dot)com> wrote
a message of 153 lines which said:

> First work out the maximum number of times each value of X will occur in the
> table

A better solution, when you do not know this maximum number, is CREATE
AGGREGATE (http://www.postgresql.org/docs/8.1/interactive/xaggr.html)
See details :

http://www.bortzmeyer.org/agregats-postgresql.html

(Yes, it is in french but the SQL examples are in english, variable
names included, so they still can be useful for the OP).