Adding a Number to a string

[simian101]

I need to combine string with a consecutive number.

Like

Option1
Option2
Option3
etc.

Is there a way to do it without using a cursor?

Somthing like

select field1, 'option' + MagicNumber as OptNum

Thanks

Simi

 

[ESquared]

Need more info about what you're trying to do. Give sample data and example expected output.

 

[simian101]

I am setting up a program that has a table that hold data to be used in drop down fields.

The table has to be formatted as

field1 field2
******* ********
option1 Bill
option2 Tom
option3 Sam

I have an existing table with field2 in it all ready.

I would like to do just do an append/insert query that appends

'Option' + str(NUMBER), NameField

I was hoping there was a way to do it without having to go line by line.

Thanks

Simi

 

[simian101]

I found an answer...

It uses a sub query.

SELECT [VECODE]
,[VENUM]
,[VEDESC]
,[VEACCT]
,[VECOMP]
,[VESUB]
,(SELECT COUNT(*) FROM [vhemast] e2 WHERE e2.[VENUM]<= e.[VENUM]) AS MyOption1
,'Option' + ltrim(str((SELECT COUNT(*) FROM [vhemast] e2 WHERE e2.[VENUM]<= e.[VENUM]))) AS MyOption2
,ltrim(str([VENUM])) + '-' + [VEDESC] as test
FROM [RepliconTemp].[dbo].[vhemast] e
order by myoption1

Thanks

Simi