Select column with row number concatenated in SQL Server

Question

I am using SQL Server 2014 and want to select a column in a table with the row number concatenated to the column value in the result set.

For example:

DemoField
---------
Apple
Ball
Cat

Should return this result set:

DemoField
---------
Row1 Apple
Row2 Ball
Row3 Cat

I went through a few similar questions where ROW_NUMBER() is used, but I find that it is selected as a separate column and not concatenated to an existing column being returned.

When I try to concatenate the ROW_NUMBER() to the column, I get an error:

Error converting data type varchar to bigint.

Please let me know.

Thanks

Answer 1

If 2012+ you can use concat()

Example

Declare @YourTable Table ([DemoField] varchar(50))
Insert Into @YourTable Values 
 ('Apple')
,('Ball')
,('Cat')

Select concat('Row',Row_Number() over(Order By DemoField),' ',DemoField)
 from @YourTable

Returns

(No column name)
Row1 Apple
Row2 Ball
Row3 Cat

Answer 2

This is just basic ROW_NUMBER with some concatenation. Seems the desired output is pretty strange but the concept is simple.

select DemoField
from
(
    select DemoField = 'Row' + convert(varchar(4), ROW_NUMBER() over (order by DemoField)) + ' ' + DemoField
    from YourTable
) x