0

I have 3 tables

Table A
Account Id         Account Name
1                  Account1
2                  Account2
3                  Account3

Table B

  Opp ID            Partner Account ID
  O11               1
  O11               2

Table C

  Opp Id           Pstring
  O11
  O12

I want to update Table C Pstring with account name, if there 2 account ids for same oppID then they should be appended with a semicolon between them and updated in Pstring

For example

  • For Opp Id O11, Pstring should have Account1;Account2

How can I achieve this? Any pointer would be of great help.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Prady
  • 10,978
  • 39
  • 124
  • 176

2 Answers2

2

Sample tables

create table A (AccountId int, AccountName varchar(100))
insert A select 1 ,'Account1'
insert A select 2 ,'Account2'
insert A select 3 ,'Account3'
create table B (OppID varchar(10), PartnerAccountID int)
insert B select 'O11', 1
insert B select 'O11', 2
create table C (OppId varchar(10), Pstring varchar(1000))
insert C (OppId) select 'O11'
insert C (OppId) select 'O12'

Create a function to concat account names for an OppID (one-off creation)

create function dbo.concatAccountName(@OppId varchar(10)) returns varchar(8000)
as
begin
declare @tmp varchar(8000)
select @tmp = isnull(@tmp+';','') + A.AccountName
from A
inner join B on A.AccountID = B.PartnerAccountID
where B.OppID=@OppId and A.AccountName is not null
return @tmp
end
GO

The update query

update C
set PString = dbo.concatAccountName(OppID)
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
1

Try something like this:

UPDATE C
SET PString = X.PString
FROM
 (
   SELECT OppID, 
       (SELECT CAST(AccountName + ';' AS VARCHAR(MAX)) 
        FROM A 
        WHERE (A.AccountID = B.PartnerAccountID) 
        FOR XML PATH ('')
      ) AS PString
   FROM B
  ) X
WHERE X.OppID = C.OppID

See also this question for more detials.

Community
  • 1
  • 1
Andrey Adamovich
  • 20,285
  • 14
  • 94
  • 132