[plug] Re: [OT] SQL column splitting

[James Devenish]

In message <Pine.LNX.4.44.0411191509320.12951-100000 at BBRH73.busby.net>
> Can you please clarify this - in terms of exactly what you want?

Check out the example from my original posting. I showed rows and
columns explicitly, both "before" and "after".

> It sounds to me (not an SQL expurt), that what you want to do, is to 
> create a view, rather than a data transformation, as you appear to want 
> to leave the original table intact.

Yes, that would be fine. I don't think this changes the question,
though.

> Do you want simply to have a routine to retrieve the data in the format 
> that you wanted, or, do you want a view created, or, do you want an 
> extra table created, that has the format that you specified?

If someone has an answer for *any* of those cases, I would be interested
to know. (If one of those cases can be answered, I assume all the others
would follow similarly). However, no one seems to have any answer that
doesn't involve a lot of procedural work.

> I had suggested Perl, as I believed that what you wanted,

No, as stated in my original posting, I was after a fairly portable SQL
solution. I realise that a Perl interpreter could be incorporated into
the SQL server, but this is not desirable for me if there is a SQL
solution instead.

> If what you wanted, was simply a report,

Nope, I wanted a SQL solution because the transformation would be
required as part of a SELECT involving JOINs. (But the involvement
of subqueries and extra tables would be fine.)

> I note that Anne had posted an SQL solution, but you appear to have not 
> responded to that one. Was that one not appropriate? She is a database 
> developer, so may be better able to help than me.

Anne's solution was presented with only a pre-determined, fixed number
of comma-separated values, and depended on various procedures. So, it
looked like it would not cope with arbitrary-length data. The reason
I did not analyse its potential arbitrary possibilities was just because
of bad timing with other events. I admin I haven't looked at it since.