Get Multiple Values in Single comun

SachinGuptha 21 Reputation points
2021-09-12T08:57:28.693+00:00

Hello All,

I have two table one is NCRTABLE one is NCR_ATTENDEETABLE, that's how values stored in both table
NCRTABLE

131208-image.png

NCR_ATTENDEETABLE

131260-image.png

i want a table with the column like this

131304-image.png

Please advise how can i achieve that

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,111 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-09-13T02:16:38.637+00:00

    Hi @SachinGuptha ,

    Welcome to Microsoft Q&A!

    Please also refer below:

     select a.*,b.AssigneeEmail   
    from NCRTABLE a  
    left join (select NCRNUMBER,string_agg(AssigneeEmail, ';') AssigneeEmail from NCR_ATTENDEETABLE group by NCRNUMBER) b  
     on a.NCRNUMBER=b.NCRNUMBER  
    

    OR

    SELECT a.*,iif(C.AssigneeEmail is not null, substring(C.AssigneeEmail,1,len(C.AssigneeEmail)-1),C.AssigneeEmail) AssigneeEmail  
    FROM NCRTABLE a  
    CROSS APPLY  
    (  
        SELECT  AssigneeEmail+';'  
        FROM NCR_ATTENDEETABLE b  
        WHERE a.NCRNUMBER = b.NCRNUMBER  
        FOR XML PATH('')  
    ) C(AssigneeEmail) ;  
    

    Output:

    NCRNUMBER	Company	Assignee	Project	AssigneeEmail  
    1	ABC	A1	P1	anon@user ;xbc@gmail.com;ybc@gmail.com  
    2	XYA	A2	P2	bcv@gmail.com  
    3	BBG	A3	P3	avc@gmail.com;akc@gmail.com  
    4	EEQ	A4	P4	NULL  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2021-09-12T11:33:08.14+00:00

    Did you already invest some effort?

    If this is an open-source homework project, then check two approaches:

    select *,
        isnull((select string_agg(AssigneeEmail, ';' ) from NCR_ATTENDEETABLE where NCRNUMBER = t.NCRNUMBER), '') as AssigneeEmail
    from NCRTABLE t
    
    -- or --    
    
    select *,
        isnull(stuff((select ';'+AssigneeEmail from NCR_ATTENDEETABLE where NCRNUMBER = t.NCRNUMBER for xml path('')), 1, 1, ''), '') as AssigneeEmail
    from NCRTABLE t
    
    0 comments No comments

  2. SachinGuptha 21 Reputation points
    2021-09-13T06:57:33.81+00:00

    Hello MelissaMa-msft and Viorel-1,

    Thank you so much for the help and time you took to look into this , i have also tried something from my end and its working for me , below is the code.

        (SELECT DISTINCT SUBSTRING
                                                                 ((SELECT        ';' + ST1.EMAIL AS [text()]
                                                                     FROM            dbo.BGS_ATTENDEETABLE ST1
                                                                     WHERE        ST1.NCRNUMBER = N.NCRNUMBER FOR XML PATH('')), 2, 1000) BGS_ATTENDEETABLE
                                   FROM            dbo.[BGS_ATTENDEETABLE] ST2
                                   WHERE        ST2.NCRNUMBER = n.NCRNUMBER ) AS [Attendees]
    
    0 comments No comments