Combining Fields with Fixed formatting

[Halliarse]

Hi

I have the following select statement:

select PurchaseOrderID, (cast([OrderDate] as VARCHAR(11)) + ' ' + cast([OrderTotal] as VARCHAR(8))) As Detail from PurchaseOrders where SupplierID = 117 and PurchaseOrders.Paid = 0

It gives the following output:

1916 Sep 21 2016 13.00
1917 Sep 27 2016 1295.89
1924 Oct 4 2016 1260.91
1925 Oct 4 2016 374.12
1929 Oct 17 2016 1416.72
1938 Nov 7 2016 1107.02
1939 Nov 8 2016 157.55
1943 Nov 14 2016 157.09
1945 Nov 15 2016 602.26
1946 Nov 15 2016 186.59
1947 Nov 16 2016 371.95
1949 Nov 21 2016 381.55

I want to change it so that the numerical output to the right (The OrderTotal included within the Detail column) is right aligned with a preceding £ sign and I am unable to work out how to do it! I've looked at loads of previous posts and not found anything specific to my requirements.

Any help please?

Many thanks

Steve

 

[Olaf Doschke]

Why cramp everything into one detail column? Even when right aligning ordertotal you still have dates with day<10 causing non alignment of the year and the rest.

An expression like [tt]Right(Replicate(' ', 8)+Convert(Varchar(8),[OrderTotal],8)[/tt] would do the trick of right alignment, as explained in thread183-1382254, for example. I simply used the search of this forum and searched "right align", isn't that hard to find.

Bye, Olaf.