[plug] Re: [OT] SQL column splitting

[Craig Ringer]

On Tue, 2004-11-16 at 09:28, James Devenish wrote:
> In message <20041116005028.GI19926 at localhost>
> on Tue, Nov 16, 2004 at 08:50:29AM +0800, PEter Crystal wrote:
> > of course, if the data is presented in just columns of data
> > in a text file perl could handle it relatively easy.
> 

> If I really wanted to get this done by any means, I could
> do a query using PHP, transmit the information via RPC to a remote
> server running an ADA programme which would do the processing in EBCDIC
> and transmit the results back to me by carrier pigeon for manual data
> entry into the RDBMS to complete the remaining joins. But that's a very
> oblique way of performing one particular query that's nested within an
> interactive application.

I'd argue that I've seen worse in the code for our booking system
(though if there are carrier pigeons, they're rather well hidden) :-P

> I was imagining it would instead be possible
> with some well-known SQL motifs. At this stage, I'm predicting the
> solution will be implemented in humans rather than machines.

It sounds to me like you're going to have to resort to using a stored
procedure. I've never had to write one that generates multiple rows of
output for each input row, but I _think_ it's possible. Hopefully you
can use an SQL stored procedure, at least if the specific database
provides the right tools.

While not overly portable, stored procedures aren't too bad so long as
you have a fairly small set of databases to support and a small number
of procedures, as the requirement for creating alternative versions for
each DB isn't too odious.

I'll see if I can write one up for PgPL/SQL - it sounds interesting. 

--
Craig Ringer