Concatenating Int Columns

Question

I have a table called Field_Data, and the data in the table looks like this:

Date Track_ID Item# 2011-02-25 00:00:00.000 70212 1 2011-02-25 00:00:00.000 70212 2 2011-03-09 00:00:00.000 70852 1 2011-03-09 00:00:00.000 70852 3 

I am trying to get output like:

 Date Final_ID 2011-02-25 00:00:00.000 70212_1 2011-02-25 00:00:00.000 70212_2 2011-03-09 00:00:00.000 70852_1 2011-03-09 00:00:00.000 70852_3 

I tried to do something like this:

 Select Date,Track_ID + '_' + Item# AS Final_ID From Field_Data 

But this gave me the following error:

Msg 245, Level 16, State 1, Line 1
Conversion error when converting varchar '_' value to int data type.

Can someone help me on how to do this?

Answer 1

You need to specify the INT fields as varchar :

 Select Date,CAST(Trakc_ID as varchar(20)) + '_' + CAST(Item# as varchar(20)) as Final_ID From Field_Data 

Answer 2

The search begins for new page visitors in SQL Server 12+, the CONCAT function is available there.

 SELECT CONCAT([Date], [TrackId], '_', [ItemNumber]) AS FinalId FROM FieldData