RE: [GENERAL][SQL] 'denormalising' with a select - Mailing list pgsql-general

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@ludwig.ucl.ac.uk]
> Sent:    Tuesday, June 01, 1999 6:54 AM
> To:    pgsql-general@postgreSQL.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@ludwig.ucl.ac.uk              |
> +-------------------------+--------------------------------------+