question

SachinGuptha-5568 avatar image
0 Votes"
SachinGuptha-5568 asked SachinGuptha-5568 answered

Get Multiple Values in Single comun

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-generalsql-server-transact-sql
image.png (5.9 KiB)
image.png (7.2 KiB)
image.png (10.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SachinGuptha-5568,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SachinGuptha-5568 avatar image
0 Votes"
SachinGuptha-5568 answered

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]



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.