pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: pgsql-sql@postgreSQL.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] 'denormalising' with a select
Date: Tue, 1 Jun 1999 16:53:17 +0300
Message-ID: <l03130302b379941204b2@[147.233.159.109]> (raw)
In-Reply-To: <v04020a00b37466607a13@[128.40.242.190]>

At 18:44 +0300 on 28/05/1999, Stuart Rison wrote:


> Can a do 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 |     |     |     |

The question is, why would you want to do something like that? It doesn't
add any more information than your basic table has. It is merely a
different way of presenting the same information.

Do you simply want to display it this way? What do you want to do with it
on the frontend?

It's not impossible. You can do this with a five-way self-join and a lot of
<> clauses in the where. It would be terribly inneficient. If all you want
is to display it like this, you should simply have your frontend read the
table ordered by brecard_id, and print the code field horizontally until it
encounters a different brecard_id. Another option is to have an aggregate
that concatenates the codes, and have your frontend decompose the resulting
string. I think the first method is easier in this particular scenario.

I don't think there is a way you can do the above in SQL without knowing
the maximum number of codes in advance.

Herouth


--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (4+ messages)  latest in thread

Message-ID: <l03130302b379941204b2@[147.233.159.109]>
Permalink:  ../l03130302b379941204b2@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130302b379941204b2@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, stuart@ludwig.ucl.ac.uk
  Subject: Re: [SQL] 'denormalising' with a select
  In-Reply-To: <l03130302b379941204b2@[147.233.159.109]>
  References: <v04020a00b37466607a13@[128.40.242.190]> <l03130302b379941204b2@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox