concatenate two numerical columns in sql server

gladstonesheeba
gladstonesheeba used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

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

SELECT @Res
AneeshDatabase Consultant
Top Expert 2009

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

SELECT @Res
select cast(2009 as varchar(4)) + right('00' + cast (02 as varchar(2)) ,2)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
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)

Author

Commented:
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....
looks like you've spaces in the column values
try this
select  distinct cast(ltrim(rtrim(columnA)) as varchar(4)) + right('00' + cast (ltrim(rtrim(columnB)) as varchar(2)) ,2)

Author

Commented:
Thank you very much
:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial