Casting fields and concat them

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Casting fields and concat them

Author

Topic

asyed01
Starting Member

13 Posts

Posted - 2009-08-21 : 09:54:08

Hello experts,

I’m new to SQL Server database and trying to pull some data. I’ve wrote the following query which give me the required results.

SELECT SUM(actualvalue_base) AS 'Actual Revenue Base', bot_salesrepidname AS 'Sales Rep', COUNT(accountidname) AS 'Accounts', bot_upgrade
FROM dbo.FilteredOpportunity
WHERE (bot_upgrade = 'True') AND (actualvalue IS NOT NULL) AND (bot_salesrepidname NOT IN ('Beth McKellar', 'Ellen Lowden'))
GROUP BY bot_salesrepidname, bot_upgrade
ORDER BY 'Sales Rep'

However ideally I want to concatenate bot_salesrepidname with bot_upgrade. After trying a few things I’ve realize that there is a data type difference. Due to this reason I’m not able to join these 2 fields. Now I’m wondering can I CAST these 2 fields? Is there any way by concatenate 2 different data types fields? Bot_salesrepidname is nvarchar and bot_upgrade is bit.

Thanks a lot in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 10:01:33

use

cast(bot_upgrade as nvarchar(100))

Madhivanan

Failing to plan is Planning to fail

asyed01
Starting Member

13 Posts

Posted - 2009-08-21 : 10:56:07

quote:
Originally posted by madhivanan

use

cast(bot_upgrade as nvarchar(100))

Madhivanan

Failing to plan is Planning to fail

asyed01
Starting Member

13 Posts

Posted - 2009-08-21 : 10:57:18

Madhivanan,you are the best. It worked. Full points to you.
Here is how my query looks like now thanks to you.

SELECT bot_salesrepidname AS 'Sales Rep', COUNT(accountidname) AS 'Accounts', SUM(actualvalue_base) AS 'Actual Revenue Base',
bot_upgrade AS 'Upgrade', bot_salesrepidname + ' has ' + CAST(bot_upgrade AS nvarchar(100)) AS Expr1
FROM dbo.FilteredOpportunity
WHERE (bot_upgrade = 'True') AND (actualvalue IS NOT NULL) AND (bot_salesrepidname NOT IN ('Beth McKellar', 'Ellen Lowden'))
GROUP BY bot_salesrepidname, bot_upgrade
ORDER BY 'Sales Rep'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 02:12:32

quote:
Originally posted by asyed01

Madhivanan,you are the best. It worked. Full points to you.
Here is how my query looks like now thanks to you.

SELECT bot_salesrepidname AS 'Sales Rep', COUNT(accountidname) AS 'Accounts', SUM(actualvalue_base) AS 'Actual Revenue Base',
bot_upgrade AS 'Upgrade', bot_salesrepidname + ' has ' + CAST(bot_upgrade AS nvarchar(100)) AS Expr1
FROM dbo.FilteredOpportunity
WHERE (bot_upgrade = 'True') AND (actualvalue IS NOT NULL) AND (bot_salesrepidname NOT IN ('Beth McKellar', 'Ellen Lowden'))
GROUP BY bot_salesrepidname, bot_upgrade
ORDER BY 'Sales Rep'

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources