0

I have a task of selecting some columns and show each set of columns in a single line, but with a required format. The first set of data will use 127 spaces, which include 'AAAA', [LV_WRR], 'D', [LV_WRR] and [ID], but [ID] varies in length, and that is the problem. [FTA] should start in the space 128, disregarding the length used by [ID].

This is what I have up to now:

SELECT
    'AAAA'
  + [LV_WRR]
  + 'D'
  + [LV_WRR]
  + [ID]
  + SPACE(120 - LEN([ID]))
  + [FTA]
  + [FT_ID]
FROM MyTable

[ID] column has the data with different lengths.

Any ideas?

Thanks.

user1330343
  • 21
  • 2
  • 7

1 Answers1

0

Try this:

SELECT 
LEFT('AAAA' + [LV_WRR] + 'D' + [LV_WRR] + [ID] + REPLICATE(' ',127),127)  + [FTA] + [FT_ID]
FROM MyTable

REPLICATE creates a 127 character long string of spaces. This is appended to the string formed by the other column values and constants. LEFT then takes the 127 leftmost characters of this string, and appends values from [FTA] and [FT_ID] columns. The result is that the value for FTA always starts at position 128.

A sample demo can be found here.

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • Thanks for your answer, but I get a different result in SQL Server 2008. 'pqrs' are not aligned. – user1330343 Jun 10 '14 at 18:27
  • I tried on SQL Server 2008 R2 and they are aligned as per demo. Could you help to set up a SQLFiddle with some sample data, so I can reproduce the result please? – shree.pat18 Jun 11 '14 at 03:53