Help with Joining Columns into New Column

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

Transact-SQL (2005)

Help with Joining Columns into New Column

Author

Topic

nothingman_cdn
Starting Member

4 Posts

Posted - 2007-03-01 : 14:12:24

Need to combine 2 columns, into a 3rd column which is empty. But 2nd column is only used when the first column has a blank.

Example:

Column 1
aa
bb

cc

dd
ee

Column 2
1
11
111
1111
11111
111111
1111111

After Join Column 3 will look like

aa
bb
111
cc
11111
dd
ee

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:19:43

How to do it depends on whether the value in Column 1 is blank or null.

If it's blank then

SELECT CASE WHEN Column1 = '' THEN Column2 ELSE Column1 END AS Column3 ....

If it's NULL then

SELECT COALESCE(Column1, Column2) AS Column3 ....

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:22:02

One more thing - just to help you understand the terminology in SQL, this is simply an expression, it's not "joining" the columns. Joins in SQL refer to queries on more than one table and the process of matching rows from the tables together to form a result table.

nothingman_cdn
Starting Member

4 Posts

Posted - 2007-03-01 : 14:32:43

quote:
Originally posted by snSQL

How to do it depends on whether the value in Column 1 is blank or null.

If it's blank then

SELECT CASE WHEN Column1 = '' THEN Column2 ELSE Column1 END AS Column3 ....

If it's NULL then

SELECT COALESCE(Column1, Column2) AS Column3 ....

I pretty new to this stuff. So can I use that case statment with update?

Example

update table
set col3 = CASE WHEN Column1 = '' THEN Column2 ELSE Column1 END AS Column3

This dosen't work when i try it. And you will laugh at what I was trying to do. But maybe you can see what I am trying to do and help elaborate alittle more. Thanks

nothingman_cdn
Starting Member

4 Posts

Posted - 2007-03-01 : 14:46:46

Also I found out. Column1 is char and column 2 is numeric. If I make column3 char should that be o.k?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:58:45

You had it almost correct, for an update you mustn't give the expression a column name, so just

update table
set col3 = CASE WHEN Column1 = '' THEN Column2 ELSE Column1 END

As far as numeric and char go, no, you cannot just assign one data type to the other. It will work sometimes, when SQL Server can do an automatic conversion, but often it will not. You use the cast or convert functions to convert data, so in your case it becomes (I used char(10), you must make it the same as the type of col3 and col1):

update table
set col3 = CASE WHEN Column1 = '' THEN cast(Column2 as char(10)) ELSE Column1 END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources