SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Cross Column Replacement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chongsajr
Starting Member

USA
2 Posts

Posted - 05/28/2013 :  17:14:38  Show Profile  Reply with Quote
Hi,
I need to report out some information, but I need to replace values in the num column of the report with a string if the value in the Type column is the value 0. Otherwise use the original value of the Num column.

For example:

Table in Database
|Type |Num |
---------------
0 | 24
2 | 35
0 | 234
2 | 12
2 | 34

Resulting table should be:

| Result |
----------
Replaced
35
Replaced
12
34


I wasn't sure how to even look that up. It's kind of complicated to explain. I hope that makes sense.

Thanks,
Stan

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 05/28/2013 :  18:24:44  Show Profile  Reply with Quote
You can do that like shown below, but the thing is that when you do that when you do that you are forced to convert the numeric data type to a string - because all the data in any column returned in a select list have to have the same data type. For a variety of reasons, ending up with a string where it should be numbers is not a good thing - you will lose the ability to do numeric sort, for one thing. So the best strategy would be to do the replacement of zero's with the strings just before presenting the data - for example, in the reporting services if that is where you are going to use the data.

In any case:
SELECT
	[Type],
	CASE WHEN [Type] = 0 THEN 'Replaced' ELSE CAST(num AS VARCHAR(32)) END AS NumString
FROM
	TableInDatabase;

Edited by - James K on 05/28/2013 18:25:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/29/2013 :  01:09:59  Show Profile  Reply with Quote
I would second James on this. Its much easier to do the display bit at front end tools rather than changing the actual data type of fields. It may have some future implications if you plan to do any further manipulations with these returned values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chongsajr
Starting Member

USA
2 Posts

Posted - 05/31/2013 :  09:37:36  Show Profile  Reply with Quote
Thanks for the responses. I making my example more generic. Basically the type is either 0 or 2. If the type is 0 then it's an employee's name. If the type is 2 it's the Project Charge Code. They basically need me to replace the people's names with the number 50100. So it would really be going from a string to a number. Thanks for the help I really appreciate it since I've been mainly doing XSL programming/testing/IT support and not a SQL programming, but when accounting says "we need this" you give it to them! haha
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/01/2013 :  05:45:54  Show Profile  Reply with Quote
quote:
Originally posted by chongsajr

Thanks for the responses. I making my example more generic. Basically the type is either 0 or 2. If the type is 0 then it's an employee's name. If the type is 2 it's the Project Charge Code. They basically need me to replace the people's names with the number 50100. So it would really be going from a string to a number. Thanks for the help I really appreciate it since I've been mainly doing XSL programming/testing/IT support and not a SQL programming, but when accounting says "we need this" you give it to them! haha


then is it that just an update you need?

like

Update TableName
SET Name=50100
WHERE Type=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000