Appending info to the output of a column

• RoadRunner

  SSC-Addicted

  Points: 409

  More actions

  April 5, 2004 at 11:16 am

  #108113

  I have to append a series of numbers to the end of a column

  in the oputput, starting at 800 and increasing by 1 for each

  record. for example: The column "document" contains the data

  AAA, BBB, CCC . . etc I need the output to look like this;

  AAA800BBB801

  CCC802

  ... etc

  How can I do this?

  TIA

• hoo-t

  SSChampion

  Points: 12713

  More actions

  April 5, 2004 at 3:48 pm

  #501811

  I'm sure someone will come up with a more elegant solution, but, here's one...

  create table #orig_table (document char(3))

  create table #temp_table (#seq int identity(800,1), #document varchar(6))

  insert #orig_table values ('AAA')

  insert #orig_table values ('BBB')

  insert #orig_table values ('CCC')

  insert #temp_table

   select document from #orig_table order by document

  select #document + cast(#seq as char(3)) as 'OUTPUT' from #temp_table order by #document

  drop table #orig_table

  drop table #temp_table

   

  OUTPUT

  --------

  AAA800

  BBB801

  CCC802

• Andrew Leighton-Sims

  SSC Enthusiast

  Points: 132

  More actions

  April 7, 2004 at 12:02 pm

  #502042

  hoo-t's reply is the best way to do it for a result set. 

  However, If you want it as a single continuous result, then try this:-

  --create test table and populate it

  create table dbo.test2

  (document varchar(3))

  Insert into test2 values ('AAA')

  Insert into test2 values ('BBB')

  Insert into test2 values ('CCC')

  --return result

  declare @dummy as varchar(2000), @int as integer

  Select @dummy='',@int=800

  Select @int=@int+1,@dummy = @Dummy + document + convert(varchar(10),@Int) from test2

  select @dummy as 'OUTPUT'

   

  OUTPUT

  ---------

  AAA801BBB802CCC803