Thread: 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.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.X limit 1)),'') ||
coalesce((':' || (select subfoo.Y from subfoo where subfoo.X = superfoo.X limit 1 offset 1)),'') ||
coalesce((':' || (select subfoo .Y from subfoowhere subfoo .X = superfoo.X limit 1 offset 2)),'') ||
coalesce((':' || (select subfoo.Y from subfoowhere subfoo.X = superfoo.X limit 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 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).