asked on

concatenate two numerical columns in sql server

I have two columns say A and B in a table in Sql server 2000

A columns has year data , ex 2008,2009,2007 etc..

B columns has month values ex 1,2,3, .....12

I want to concatenate both fields as YYYYMM , for example 200801,200902...etc.

Please help me out.

Aneesh

DECLARE @res varchar(8000)
SELECT @Res = COALESCE(@Res+',' , '') +CAST ([Year] as varchar) +',' +CAST([Month] as varchar )
FROM urTable

SELECT @Res

Aneesh

DECLARE @res varchar(8000)
SELECT @Res = COALESCE(@Res+',' , '') +CAST ([Year] as varchar) +CAST([Month] as varchar )
FROM urTable

SELECT @Res

mohan_sekar

select cast(2009 as varchar(4)) + right('00' + cast (02 as varchar(2)) ,2)

gladstonesheeba

ASKER

nothing is correct.

column A
(Year values) column B (Month Values)
2008 1
2009 2
2007 3

I want the concatenation to be in the select statement and the data should look like this

200801
200902
200703.

Hope you understood this.

mohan_sekar

What is not correct, gladstonesheeba? You just had to substitute column names in my query!

select cast(columnA as varchar(4)) + right('00' + cast (columnB as varchar(2)) ,2)

gladstonesheeba

ASKER

This is the result i got when i use this query

select distinct cast(columnA as varchar(4)) + right('00' + cast (columnB as varchar(2)) ,2)

2007 1
2009 1
2010
2005
2007
2006
2006 1
NULL
2008
2008 1
2009

There are month values for all of them but its concatenating only 2008, 2007,2006 that too with spaces
it should be 200801 etc....

ASKER CERTIFIED SOLUTION

mohan_sekar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gladstonesheeba

ASKER

Thank you very much

mohan_sekar