asked on
concat function in sql
Please advise how to concat/merge two sql statements results in UPDATE clause with ; symbol.
For ex:,
SQL1:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[ACR] = [UserInfo].[ID]
Output of above query is XXX.
SQL2:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[CSR] = [UserInfo].[ID]
Output of above query is YYY.
Update T1
set Col1={SQL1 concat SQL2}
Final results of T1 (required output):
Col1
=====
XXX;YYY
For ex:,
SQL1:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[ACR] = [UserInfo].[ID]
Output of above query is XXX.
SQL2:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[CSR] = [UserInfo].[ID]
Output of above query is YYY.
Update T1
set Col1={SQL1 concat SQL2}
Final results of T1 (required output):
Col1
=====
XXX;YYY
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Louis01
update T1 set C1 = (Sql1) + (Sql2)
Here's one way:
declare @res varchar(max);
set @res = (select '1') + ';' + (select '2');
select @res;
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Or even cut out all the variables...
update T1 set C1 = (select '1') + (select '2')
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Qlemo is quite right about the addition of varchar v.s. numeric (except if there is a ';' between, then you'll get a conversion failure).
If you have numeric results: To get around the conversion failure, use the convert / cast function
If you have numeric results: To get around the conversion failure, use the convert / cast function
ASKER
--
Sorry, but I don't agree to the closure.
a) http:#a39767405 isn't showing the solution. It is rather http:#a39767408 .
b) My post http:#a39767431 is on the point, and contains all data you provided, while http:#a39767408 is very generic (and long-winded).
I would have expected at least an equal split.
a) http:#a39767405 isn't showing the solution. It is rather http:#a39767408 .
b) My post http:#a39767431 is on the point, and contains all data you provided, while http:#a39767408 is very generic (and long-winded).
I would have expected at least an equal split.
I agree