How to combine two text columns

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

How to combine two text columns

Author

Topic

exp2000
Starting Member

5 Posts

Posted - 2006-06-09 : 10:41:07

Hi I have a table that has two columns whose values need to be combined into one column and pipe delimed into the first one.
But I am not sure how to writed the query.

Here is my atmempt, which by the way does not work, but you might understand what I am trying to do.

update tabmodulesettings
set settingvalue =
(Select settingvalue from tabmodulesettings as t2 where t2.settingname='m2' and t.tabmoduleid=t2.tabmoduleid) + '|' +
(select settingvalue from tabmodulesettings as t3 where t3.settingname='m7' and t.tabmoduleid=t3.tabmoduleid)
from tabmodulesettings t
where settingname='m2'

winrodri
Starting Member

2 Posts

Posted - 2006-06-09 : 11:04:43

i tried it in another table..
this is the result :
there is a users table...having
userid
firstname
title
i have updated the firstname field by concatenating it with a '|'
update users
set firstname =
(select firstname
from users
where userid = 1)
+ '|' +
(select title
from users
where userid = 1
)
where userid = 1

hope it solves ur prob..if not do reply back...ill be glad to help

exp2000
Starting Member

5 Posts

Posted - 2006-06-09 : 11:31:27

well, this will not work, because I did not give a good example.
Here is how the table looks like

TabModuleId SettingName SettingValue

127 m2 164
127 m7 176
127 s1 143
etc.

So for each setting there is a row. Here is a result that I need to get:

127 m2 164|176

I need to find all rows that have both settings for the same tabmodule id and cobine m2 and m7 where m2 becomes m2 | m7

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 04:12:22

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-11 : 06:16:25

Is your expected correct??
According to what you have specified it should be somthing like this

127 m2 167|176|143

if you grouping on TabModuleID..

Please post some vast range of sample data and expected output

This thing cant be done in single update statment, i guess you need to run update and delete in tandom..

Chirag

exp2000
Starting Member

5 Posts

Posted - 2006-06-11 : 08:42:32

Here are the statments that worked perfectly. Thanks for you help guys

Update TabModuleSettings
Set SettingValue = a.SettingValue + '|' + b.SettingValue
From TabModuleSettings a,
(Select TabModuleId,SettingName,SettingValue
From TabModuleSettings Where SettingName='m7' ) b
Where a.SettingName='m2' and a.TabModuleId=b.TabModuleId

delete from tabmodulesettings where settingname='m7'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources