Defining multiple fields in a select string with hard set length values (SQL 2000)
First - note that performing detailed formatting in SQL is often frowned upon, and "should be left to the calling application". Such recommendations, of course, ignore situations like this where there is no calling application.

I don't see an issue with the fundamental methodology seen in the query you provided. In a situation like this, it's wise to use application-specific knowledge. For variable-length values that you're willing to truncate, you need to figure out what length covers most of the data, and provides enough of that column to make out what it is. For values that must be provided as is (your numeric data), you need a length will cover the maximum possible value for the field. And, that may be different for different columns: a quantity column in a table to hold data on cars at a dealership can probably be smaller than a "total value" column in the same table.

If the hard-coded values you're using need to be used on a number of lines, you can put them into variables.

I would recommend making sure that you have "gutters" between your columns - 1-3 spaces between each column helps keep them distinct and easily readable. I'm pretty sure the "LD-MOLDINLD-" lines in your sample output are run together due to the lack of a gutter.

Finally, you might want to flip the padding on the numeric columns; if you change:

LEFT(CONVERT(varchar(7), ordered) + '       ', 7)


to:

RIGHT('       ' + CONVERT(varchar(7), ordered), 7)


then the numbers will be right-aligned, which is far more typical for numbers.



All that said, none of this works well if your carefully spaces output isn't displayed using a mono-space font.

Looking at "MACHINE", "SHIPPING", and "MOLDINLD", I suspect the output you've provided a screen shot of is not using a mono-spaced font. Note that "MACHINE" (7 letters) and "SHIPPING" (8 letters) seem like they line up on their right side (or are off by 1 pixel).

Not knowing your email program, I can't be sure of the best way to convince it to display this data that way. For HTML output, try enclosing the entire output block in &lt;pre&gt;&lt;code&gt; tags (with appropriate closing &lt;/code&gt;&lt;/pre&gt; tags at the end. As, in fact, is done on this very page at this time (right-click, select "View source", and scroll a ways to see).

You can confirm this is correct by copying the output you took a screenshot of, and pasting it into a text editor set to use a mono-space font.