gladstonesheeba
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.
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.
DECLARE @res varchar(8000)
SELECT @Res = COALESCE(@Res+',' , '') +CAST ([Year] as varchar) +CAST([Month] as varchar )
FROM urTable
SELECT @Res
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)
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.
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)
select cast(columnA as varchar(4)) + right('00' + cast (columnB as varchar(2)) ,2)
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....
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much
:)
SELECT @Res = COALESCE(@Res+',' , '') +CAST ([Year] as varchar) +',' +CAST([Month] as varchar )
FROM urTable
SELECT @Res