Re: SQL Query
| От | Steve Grey |
|---|---|
| Тема | Re: SQL Query |
| Дата | |
| Msg-id | c0c351c40712050343r12ba7a6fnb8f1fefe718f432@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: SQL Query (Ashish Karalkar <ashish_postgre@yahoo.co.in>) |
| Ответы |
Re: SQL Query
(Stephane Bortzmeyer <bortzmeyer@nic.fr>)
|
| Список | pgsql-general |
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
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 Dec 5, 2007 10:47 AM, Ashish Karalkar <ashish_postgre@yahoo.co.in> wrote:
am Wed, dem 05.12.2007, um 10:24:04 +0000 mailte Ashish Karalkar folgendes:Thanks Andreas for your replay.
> 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
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.
В списке pgsql-general по дате отправления: