Align column in SQL Query output

1

Hi SQL Expert, I am trying several SQL statements to combine DB and CR column but I cannot seem to get the CR column to align underneath each other when the DB column has Variable length characters , below are some that I tried and the result I am getting, just cant seem to get the CR to all align underneath each other, any help will be appreciated.

('DB ' + GLD.GLdisplay + ' ' + 'CR '+GLC.GLdisplay) AS Account_No

LEFT('DB ' + GLD.GLdisplay + REPLICATE(' ', 15), 15) +
SPACE(2) +
'CR ' + GLC.GLdisplay AS Account_No

this produce this result, the CR is not aligned under each other
DB 95-101-325 CR 95-770
DB 95-101-262 CR 95-770
DB 95-101-750 CR 95-770
DB 90-101 CR 90-471-01
DB 90-101 CR 90-471-02

DB 90-101 CR 90-471-92
DB 90-101 CR 90-471-92
DB 90-101-02 CR 90-471-92
DB 90-101-02 CR 90-471-92
DB 90-101-02 CR 90-471-92
DB 90-101-02 CR 90-471-92
DB 90-101 CR 90-471-92
DB 90-101 CR 90-471-92

This is what I would like the output from the Query to look like
DB 95-101-325 CR 95-770
DB 95-101-262 CR 95-770
DB 95-101-750 CR 95-770
DB 90-101 CR 90-471-01
DB 90-101 CR 90-471-02

2

This is an image of what the output actually look like,

image

This is what I would like the output from the Query to look like
DB 95-101-325 CR 95-770
DB 95-101-262 CR 95-770
DB 95-101-750 CR 95-770
DB 90-101 CR 90-471-01
DB 90-101 CR 90-471-02

This is what I would like it to look like

image

3

Presentation of data is not SQL Server job. Do that in your front end application where the data is presented to user.

4

You might look at using REPLICATE(' ', 30) to create a fixed width empty string, then using STUFF() to overlay the data elements where you want them to go.

5

Change the output from grid to text - aligning characters won't show up in the grid as you expect, but will show up in text.

6

It is when that's the only output you have. :yum:

1 Like
7

First of all, it's not going to matter much if you use either the GRID or TEXT outputs for this. The key is that the FONT must be a fixed width font like Consolas or Courier New.

Then, going back to your first post, you need something like the following...

CONCAT('DB ',CONVERT(CHAR(15),GLD.GLdisplay),' CR ',GLC.GLdisplay) AS Account_No

Notice how we simply converted GLD.GLdisplay to a CHAR(15). It can be that easy.

8

Meanwhile, I'm over here suggesting using STUFF(), like:
fb1

1 Like
9

BTW and speaking of "fixed font", if you want to display vertically aligned text on this forum using leading or multiple embed spaces, you simply need to use the "Pre-formatted Text" option (Icon that looks like image ) from the "Toolbar" on the edit window. Most folks use that to post code so the indentation and spacing will be nice but it works on text for data, as well.

For example, the following data had the correct embedded multiple spaces when I posted it. The forum software removes the embedded multiple spaces for display.
DB 95-101-325 CR 95-770
DB 95-101-262 CR 95-770
DB 95-101-750 CR 95-770
DB 90-101 CR 90-471-01
DB 90-101 CR 90-471-02

Here's the exact same text as above but posted as "Pre-formatted Text" using the icon I pointed out above. You can also use ``` on separate lines both above and below the text you paste or line up.

Also notice, the edit window has a non-fixed font and so you have to either create your text in something like notepad (or using the CHAR() trick in SQL) or you need to look at the "preview " window at you're typing.

DB 95-101-325    CR 95-770
DB 95-101-262    CR 95-770
DB 95-101-750    CR 95-770
DB 90-101        CR 90-471-01
DB 90-101        CR 90-471-02
10

Not to worry... been there and done that and that's why I know an "easy" way now.

rofl

11

Thank you all for your response to my question, I've tried every suggestion and still have the same issue, I am using Consolas fonts and with Jeff
CONCAT('DB ',CONVERT(CHAR(15),GLD.GLdisplay),' CR ',GLC.GLdisplay) AS Acct
and this is what I am getting, this is used for stimulsoft report so what the Query show is what is shown in report, at this point I am thinking something is wrong with my SQL or Query. this is close so I am hoping the client don't complain about the CR not vertically aligned.

image

12

Where are you getting the output from? The SSMS grid or text window or something else? If it's something else, it's not the Consolas font because the dashes are much skinnier that the letter "C" for example.

In other words, whatever is producing that output is NOT set for a fixed width font.

13

if you are using a reporting tool, why don't you display the Debit and Credit under separate column ?

1 Like