Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Combining 2 numerical results without doing a sum MS SQL 2005

Posted on 2011-03-14
Medium Priority
212 Views
I want to combine 2 numerical results from different columns into one result that i save in a third column,

the stament i have at the moment is:

update myTable set myCol = 2.55 where something = something

i want to do something like this

update myTable set myCol = 2.55 + / + 19 where something = something
and get this result
2.55/19
when i try that above, i get an error
0
Question by:QuinnDester
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 5
• 2

LVL 9

Expert Comment

ID: 35127043
try:-

``````update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/ '+ cast( 19 as NVARCHAR(50))  where something = something
``````
0

LVL 9

Assisted Solution

mayank_joshi earned 2000 total points
ID: 35127054
you may also do:-

``````update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/ '+ cast(col2 as NVARCHAR(50))  where something = something
``````

where col1 and col2 are field names.
0

LVL 9

Expert Comment

ID: 35127079
sorry i wrote an extra empty space in '/ '
Correct query:-

``````update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/'+ cast( 19 as NVARCHAR(50))  where something = something

update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/'+ cast(col2 as NVARCHAR(50))  where something = something
``````
0

LVL 19

Expert Comment

ID: 35127132
Hi,

if its hard code value then you no need to do cast
this will do

update myTable set myCol = '2.55/19'
0

LVL 3

Author Comment

ID: 35127147
it is complicated slightly by using dynamic sql, what you have given me works, but putting it into dynamic sql it isnt saving anything after the col1, but is saving the col1 ??

here is what i am using

'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0

LVL 9

Expert Comment

ID: 35127181
why are you using double quotes(") ?
0

LVL 3

Author Comment

ID: 35127184
the reason for using dynaimc sql, is the col name on one of the tables is decided dynamicaly, there is no way around that that i know of.
0

LVL 19

Expert Comment

ID: 35127185
Hi,

can you post your full code?
0

LVL 3

Author Comment

ID: 35127186
double quots are so i get a single quote when the dynamic sql is executed
0

LVL 3

Author Comment

ID: 35127195
that is the full statment conserned, but if you think it will help the full sp is below
``````USE [MainDB]
GO
/****** Object:  StoredProcedure [dbo].[UpdateSystemData]    Script Date: 03/14/2011 09:40:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
@name varchar (Max),
@status varchar (Max),
@player varchar (Max),
@alliance varchar (Max),
@Position int,
@Galaxy int,
@System int,
@HasMoon bit,
@date datetime,
@Coordinates nvarchar (50)
)
AS
BEGIN
if @player not like 'Joshua Calvert'
begin
update planets set invalid = 0, coordinates = @Coordinates, timelastupdated = @date, [name] = @name, player=@player, alliance=@alliance, hasmoon=@HasMoon
where galaxy=@Galaxy and [system]=@System  and position=@Position

declare @col varchar (max)
declare @sql nchar (500)
declare @thishour int
declare @lastupdated datetime
declare @updatedcount int
declare @avgstatus dec(12,2)

set @thishour = (select top 1 DATEPART(hour, @date))
set @lastupdated = (select lastupdated from activity_history where Coordinates = @Coordinates)
set @col = (select hour_words from convert_hour where hour_numbers = @thishour)

if @lastupdated <= dateadd(hour, -1, getdate()) or @lastupdated is null
begin
set @updatedcount = (select updatedcount + 1 from activity_history where  Coordinates = @Coordinates)
update activity_history set updatedcount = @updatedcount, LastUpdated = getdate() where  Coordinates = @Coordinates
set @sql = 'update activity_history set '+
cast(@col as varchar (max))+ ' = '+ cast(@col as varchar (max))+' + '+ cast(@status as varchar (max)) +'
where Coordinates =  '''+ cast(@Coordinates as varchar (max)) +'''declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''

exec (@sql)

--declare @sql1 nchar (500)
--set @sql1 = 'update planets set [status] = (select dbo.avg1('+cast(@thishour as varchar (max))+',updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')'
--exec (@sql1)
end
else
begin
set @sql = 'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+' ,updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = @avgstatus where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''

exec (@sql)

end

end
END
``````
0

LVL 9

Expert Comment

ID: 35127215
this should work:-

``````update planets set [status] = @avgstatus where Coordinates = cast(@Coordinates  as varchar (max))
``````
0

LVL 3

Author Comment

ID: 35127241
that part in the in the else statment works fine, its the if statment where i have tried to add the extra info to that statment where it not working...
0

LVL 3

Accepted Solution

QuinnDester earned 0 total points
ID: 35127369
got it...

this works, though the only difference is a space in a place where i though a space wouldnt matter, obviously it does :)

'update planets set [status] = cast(@avgstatus as varchar (max)) +''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0

LVL 3

Author Closing Comment

ID: 35170629
my solution fixed the problem
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can reâ€¦
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll