Lists: | pgsql-general |
---|
From: | "Bt" <jacky(at)xtrapower(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | table linking problem |
Date: | 2002-10-04 16:35:00 |
Message-ID: | ankfrn$iib$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi all,
I have two table, follow :
content
content_id | material_id | percentage
-----------------------------------------------------
1 1 100%
2 2 63.5%
2 3 31.5%
2 4 5%
material
material_id | name
------------------------------------------
1 COTTON
2 POLYESTER
3 NYLON
4 SPANDEX
The Question is,
How can I get like this :
100% COTTON
63.5% POLYESTER 31.5% NYLON 5% SPANDEX
Can I return the string like this ?
Thanks.
From: | "Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: table linking problem |
Date: | 2002-10-07 13:52:06 |
Message-ID: | 3DA1DEDE.6030.1067C81A@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 5 Oct 2002 at 0:35, XBXtXX wrote:
> content
> content_id | material_id | percentage
> -----------------------------------------------------
> 1 1 100%
> 2 2 63.5%
> 2 3 31.5%
> 2 4 5%
>
> material
> material_id | name
> ------------------------------------------
> 1 COTTON
> 2 POLYESTER
> 3 NYLON
> 4 SPANDEX
>
> The Question is,
> How can I get like this :
> 100% COTTON
> 63.5% POLYESTER 31.5% NYLON 5% SPANDEX
select a.percentage,b.name from content a, material b where
a.material_id=b.material_id;
HTH
Bye
Shridhar
--
kern, v.: 1. To pack type together as tightly as the kernels on an ear of corn.
2. In parts of Brooklyn and Queens, N.Y., a small, metal object used as part
of the monetary system.
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | ¼B¤tºÆ <jacky(at)xtrapower(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: table linking problem |
Date: | 2002-10-07 14:04:40 |
Message-ID: | 200210071504.40873.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Friday 04 Oct 2002 5:35 pm, ¼B¤tºÆ wrote:
> Hi all,
> I have two table, follow :
>
> content
> content_id | material_id | percentage
> -----------------------------------------------------
> 1 1 100%
> 2 2 63.5%
> 2 3 31.5%
> 2 4 5%
>
> material
> material_id | name
> ------------------------------------------
> 1 COTTON
> 2 POLYESTER
> 3 NYLON
> 4 SPANDEX
>
> The Question is,
> How can I get like this :
> 100% COTTON
> 63.5% POLYESTER 31.5% NYLON 5% SPANDEX
>
> Can I return the string like this ?
Not using SQL - you could define a function: material_names(int4) returning
text - provide it the "content_id" and it does the lookup and builds the
string required. You could do this with plpgsql, it's a simple language but
make sure you read the manual first. You could also use tcl/perl/c - see the
procedural languages chapter for details.
The other option is to do this in the client application.
- Richard Huxton
From: | Holger Klawitter <lists(at)klawitter(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: table linking problem |
Date: | 2002-10-07 14:51:47 |
Message-ID: | 200210071651.47143.lists@klawitter.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> The Question is,
> How can I get like this :
> 100% COTTON
> 63.5% POLYESTER 31.5% NYLON 5% SPANDEX
>
> Can I return the string like this ?
Yes, it can be done with plpgsql.
You need the group operation and create an aggregate like this:
create function str_append( text, text ) returns text as '
begin
if $1 isnull then
return $2;
else
return $1 || '' '' || $2;
end if;
end;' language 'plpgsql';
create aggregate str_concat (
basetype = text,
sfunc = str_append,
stype = text
);
select str_concat( c.percentage || ' ' || m.name )
from content c, material m
where c.material_id = m.material_id
group by c.content_id;
With kind regards / mit freundlichem Gruß
Holger Klawitter
--
Holger Klawitter http://www.klawitter.de
lists(at)klawitter(dot)de