CODEWITHSUNDEEP
sqlsql-servert-sql
Vishwas
Vishwas

Reputation: 1498

Select column with row number concatenated in SQL Server

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

Upvotes: 2

Views: 12823

Answers (6)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

You can also use CONCAT() as well as USE CAST() for the row number to convert it to character

SELECT CONCAT('Row',CAST(ROW_NUMBER() OVER (ORDER BY demofield) AS VARCHAR),' ', demofield)

Upvotes: 0

PreQL
PreQL

Reputation: 358

The reason this is happening is because you're trying to add a number to a string. You have to CAST your row_number as a VARCHAR i.e. 

'Row' + CAST(ROW_NUMBER() OVER (ORDER BY DemoField) AS VARCHAR)

Upvotes: 1

xGeo
xGeo

Reputation: 2139

If you want to get row number concatenated to any column, check the other answers. But IMO, that is really odd.

If you want to get row number in a separate column,

you can use ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER(ORDER BY some_column) as RowNumber FROM YourTable

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 6

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 2

GrantD
GrantD

Reputation: 64

You can use the following - 

SELECT CAST(ID AS NVARCHAR)+' '+CAST(ColName AS NVARCHAR) 
  FROM tb_Table

Upvotes: 0

Related Questions