Cross Column Replacement - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Cross Column Replacement

Author

Topic

chongsajr
Starting Member

2 Posts

Posted - 2013-05-28 : 17:14:38

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-28 : 18:24:44

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:09:59

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

chongsajr
Starting Member

2 Posts

Posted - 2013-05-31 : 09:37:36

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:45:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources