tarr94 (5/6/2016)
Thank you for the responses! I'll give this a try. Looks like the solution might have been simpler than I had expected.I've updated my original post to fix the PaddedString values. Not sure how I missed that.
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
A simple example of a potential problem is the need for one of the pieces (such as order number) to be displayed by itself on another part of the report/form. If you build the string in sql, then you either need to bring back order number by itself in an additional column, or build some string splitter logic in the report.
Why are you trying to move away from keeping this kind of logic in Crystal formulas? That's what the reporting tool is really good at.
Don Simpson