Concatenate two field into one

Question

Post reply

Concatenate two field into one

komal145

SSCrazy Eights

Points: 9918
More actions
September 1, 2023 at 9:06 pm

#4277801
hi,
I have two fields USERID and AlternateUserid in a table A(users table)
Alternateuserid is always one , for example: 12345 , for this USERID there can be many USERID: Abc001 , DEF0002 etc
TABLE A
Alternateuserid USERID
12345 Abc001
12345 Abc002
6789 bcf001
6789 bcf002
8901 ccfe001 -----------------this user has only one recor in table , so insert into emple table as it is ( no need to concatenate)
So question , when i insert the above table Table A , into employee table ( fields EMPLE , USERID ) , i need to insert something like this :
EMPLE USERID
12345 Abc001 , Abc002
6789 bcf001,bcf002
8901 ccfe001
in future , if i have more id's added to the user 12345 ( DEF0002 , FEG003 ) in TABLE A , i need to update this user : 12345 in EMPLE table USER ID field to : Abc001 , Abc002 ,DEF0002 , FEG003
How can i achieve this ?
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.
- This topic was modified 1 year, 3 months ago by komal145.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Show me where in Table "A" that you have the value "DEF0002".

I think your example data for Table "A" is incorrect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246977 More actions · Answer 2

I've read through the question a couple of times and I don't understand what you are trying to achieve.

Can you provide your sample data in consumable format, along with an example of how you would like it to look after this happens, please?

in future , if i have more id's i need to update this USER ID field to : Abc001 , DEF0002 , FEG003

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 3

sorry...i updated the question now. with correct example.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 4

Here's a SELECT query which does the concatenation. I'm sure that you can change it into a conditional UPDATE.

DROP TABLE IF EXISTS #TableA;

CREATE TABLE #TableA
(
    AlternateUserId VARCHAR(500)
   ,UserId VARCHAR(100)
);

INSERT #TableA
(
    AlternateUserId
   ,UserId
)
VALUES
('12345', 'ABCoo1')
,('12345', 'ABCoo2')
,('12345', 'DEFoo1')
,('6789', 'BCF002');

SELECT ta.AlternateUserId
      ,UserIdList = STRING_AGG (ta.UserId, ',') WITHIN GROUP(ORDER BY ta.UserId)
FROM #TableA ta
GROUP BY ta.AlternateUserId;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246977 More actions · Answer 5

Just noticed that this is a 2012 forum. You'll have to use the old FOR XML PATH method to do the concatenation.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel