SQL Query

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.kretschmer@schollglas.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@yahoo.co.in> wrote:

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

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@gmail.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).