Select row above and row(s) between next matching row or last row

Answer 1

Wow, what a mess.

Although I hate recommending the use of cursors, I don't see any other way to get the data you want. I don't think standard set based operations (selects with joins etc) are going to pull the data in any reasonable matter.

Which leaves us with 3 potential solutions.

One, do the processing in your code. This has many obvious drawbacks depending on the size of your data set.

Two, do the processing using cursors. Cursors are not very performant, but with your data structured that way performance isn't even on the radar.

Three, use some type of CLR based solution. The drawbacks here are your ability to install it and whether your sql server supports that.

Good luck.

Answer 2

That is treally complex and I'm not sure I've fully understood it but I think good output would look like:

ParentID ChildID ItemNo Description (as a resultset)

with all B/O, B/O INFO and S/ translated into parent/child relationships, and parent ID blank for root orders. This requires a table of order lines (and relationships) to be built in memory during a single pass through the source table.

If building it up in memory is not acceptable, and you have performance issues, I suggest you consider outputting this single resultset instead with two passes:

ChildID ItemNo Description (with S/ B/O B/O INFO filtered out)

UNION

ChildID ItemNo Description (with ItemNo always zero and Description set to ParentID)

If you have to put the result somewhere temporarily it should go in another table space.

And validate the parent-child relationships in the consumer of the resultset output (eg Excel or your DSS package).

This way at least some translation/compaction/rationalisation is occurring, the network use is reduced and the key fields are less kind of well, obfuscated. This may be a better solution but it is not clear from the question what the priorities and issues are.

This sounds very like real life. An elegant (and probably even recommendable) solution free zone.

EDIT

It may be harder than I was suggesting to bring the network use down.

In the two-pass solution, I suggest the B/O subsections are given keys like

97387326 (before B/O or B/O INFO section)

97387326-1 (part of first B/O or B/O INFO section)

97387326-2 (part of second B/O or B/O INFO section)

(etc)