asked on

How to create a MS SQL Query that will include a concatenation of data from another table based on id into a single column

I am writing a script to export exchange message tracking information into SQL so it can be queried by an analyst.
I stored the a 4 tables
messagelog, contacts, event, messagerecipients.
I am trying to build a query that will produce a separate row for each recipient and another query that will combine all the recipients into a comma separated list into one column. Any guidance/answers?

I store each email only once in the contacts table and the event type (SEND,RECEIVE...) once in the event table.

The messagelog table is as follows:
id,eventid,sender,subject,exmessageid,time

I have a messagerecipients table that is a map of recipients to messagelog:
id,contactid,messagelogid

Table data

--contacts
id      email
21      externaluser@domain.com
19      noreply@domain.com
17      noreply@email.education.com
13      noreply2@domain.com
23      person1@somewhere.com
24      person2@somewhere.com
25      person3@somewhere.com
22      staff.five@example.com
16      staff.four@example.com
14      staff.one@example.com
18      staff.six@example.com
15      staff.three@domain.com
20      staff.two@example.com

--event
id      type
1      BADMAIL
2      DELIVER
3      DSN
4      FAIL
5      POISONMESSAGE
6      EXPAND
7      RECEIVE
8      RESOLVE
9      SEND
10      SUBMIT
11      TRANSFER

--messagelog
id      eventid      sender      subject      exmessageid            time
1            7            13                                    8325692                  2018-02-10 16:00:12.000
2            7            15                                    8325693                  2018-02-10 16:03:11.000
3            7            17                                    8325694                  2018-02-10 16:03:55.000
4            7            19                                    8325695                  2018-02-10 16:04:04.000
5            7            21                                    8325696                  2018-02-10 16:05:07.000

--messagerecipients
id      contactid      messagelogid
1      14                  1
2      16                  2
3      18                  3
4      20                  4
5      22                  5
6      23                  3
7      24                  4
8      25                  5

byt3

ASKER

Okay, so I figured out the row per recipient query, still working on the query with all recipients concatenated into one column. Here's the query I built. If there is a better way to do it, let me know.

SELECT ml.EventID,ml.Sender,r.email AS Recipient,ml.Subject,ml.ExMessageID,ml.Time FROM messagerecipients
LEFT JOIN (SELECT m.id,e.type AS EventID,c.email AS Sender,m.subject AS Subject,m.exmessageid AS ExMessageID,m.time AS TIME FROM messagelog m LEFT JOIN event e ON e.id=m.eventid LEFT JOIN contacts c ON c.id=m.sender) ml 
ON ml.id=messagerecipients.messagelogid
LEFT JOIN contacts r ON messagerecipients.contactid=r.id

Open in new window

SOLUTION