Concatenate two field into one

  • 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 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
    • This topic was modified 9 months, 3 weeks ago by  komal145.
  • 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)

  • 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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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