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
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
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 abc@gmail.com;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.
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
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]
17 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index