Get Multiple Values in Single comun

Question

Hello All,

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

NCR_ATTENDEETABLE

i want a table with the column like this

Please advise how can i achieve that

Answer 1

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.

Answer 2

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

Answer 3

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]