RE: [GENERAL][SQL] 'denormalising' with a select

I would write a function that returns a text containing a list of all codes
with the brecard_id. Here is an example of the select statement:

select distinct brecard_id || get_codes(brecard_id) from table_name;

Here is an example of the function (not syntactically correct):

create function get_codes(int4) returns text as '
declare
rec record;
rc text;
sep text;
begin
rc := '''';
sep := '''';
for each rec in select distinct code from table_name where
brecard_id = $1;
rc := (rc || sep) || rec.code; -- parens are not needed in
version 6.5
sep := '', '';
end for
return rc;
end' language plpsql

I am not sure how get the codes to become separate fields in the select
statement. If a code_list field will work instead of separate fields for
each code then the above example should work great. I have also done a
similar thing using aggregates but the example is more complicated. Using
an aggregate function will perform better than the above example.

Thanks, Michael

> -----Original Message-----
> From: Stuart Rison [SMTP:stuart(at)ludwig(dot)ucl(dot)ac(dot)uk]
> Sent: Tuesday, June 01, 1999 6:54 AM
> To: pgsql-general(at)postgreSQL(dot)org
> Subject: [GENERAL][SQL] 'denormalising' with a select
>
> Hi there,
>
> This was posted to SQL where it 'truly' belongs but I got no answwer and
> since it has a bit of database design in it (and a lot more people seem to
> read [GENERAL]) I thought I'd try it here.
>
> Consider a table like this:
>
> brecard_id |code
> ----------------+----
> IEGA18051999006 |COME
> IPHA04031999004 |CRIB
> IPHA04031999005 |COME
> IPHA04031999005 |CRIB
> IPHA26021999006 |SOLI
> IPHA26021999010 |COME
> IPHA26021999010 |SOLI
> ISTL04031999001 |CRIB
> IUCH03031999003 |COME
> IUCH03031999003 |CRIB
> IUCH03031999003 |MICR
> IUCH03031999003 |SOLI
>
> each combination of id and code is unique (they form a composite primary
> key)
> but any brecard_id could have 1 or more codes associated with it
> (theoretically with no upper boundary but let us say a maximum of 5
> codes).
>
> Is there a SELECT which will turn each of the codes for one brecard_id
> into
> a column... ie.
>
> brecard_id |code1|code2|code3|code4|code5
> ----------------+-----+-----+-----+-----+-----
> IEGA18051999006 |COME | | | |
> IPHA04031999004 |CRIB | | | |
> IPHA04031999005 |COME |CRIB | | |
> IPHA26021999006 |SOLI | | | |
> IPHA26021999010 |COME |SOLI | | |
> ISTL04031999001 |CRIB | | | |
> IUCH03031999003 |COME |CRIB |MICR | |
> IUCH03031999003 |SOLI | | | |
>
> and here a a few more brainteasers for you gurus out there...
>
> 1) I'm actually not fussed about the order the codes appear in the
> columns,
> but let's say the order mattered, would this affect the SELECT(s)?
> 2) Would it make the query easier if I knew the maximum number of codes
> one
> brecard_id could have?
> 3) (this one for true Wizards -and Sorceresses, Herouth ;)- only) Could
> you
> write a 'generalised' query which could cope with tables having variable
> 'maximum' numbers of codes associated with each brecard_id?
>
> For the life of me I don't know how you'd do it in SQL, I have PL/pgSQL
> and
> have started playing around with that but I'd hate to re-invent the wheel!
>
> regards,
>
> Stuart.
>
> +-------------------------+--------------------------------------+
> | Stuart Rison | Ludwig Institute for Cancer Research |
> +-------------------------+ 91 Riding House Street |
> | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
> | Fax. (0171) 878 4040 | stuart(at)ludwig(dot)ucl(dot)ac(dot)uk |
> +-------------------------+--------------------------------------+