title: SQL query with comma separated values
body: <p>I have a requirement wherein I have 3 tables </p>
  
  <p><strong>ContactInfo</strong> </p>
  
  <pre><code>idContactinfo  FirstName  Lastname      idCOmpanyid
  ---------------------------------------------------
  1               Blah1      Blah1            1
  2               Blah2      Blah2            1
  3               Blah3      Blah3            1
  4               Blah4      Blah4            1  
  </code></pre>
  
  <p><strong>ContactRole</strong></p>
  
  <pre><code>idcontactrole     IdContactRoleName      idContactInfo   
  ------------------------------------------------------
  1                       1                     1
  2                       1                     2
  3                       1                     3
  4                       2                     4
  </code></pre>
  
  <p><strong>IdContactRoleLookup</strong> </p>
  
  <pre><code>idcontactRole           RoleName
  ----------------------------------
  1                        Admin
  2                        Secretary
  </code></pre>
  
  <p>I need a query that gives me a result like this </p>
  
  <pre><code>idcontactrolename            Contacts
  --------------------------------------------------------
  Admin                    Blah1,Blah1;Blah2,Blah2,Blah3,Blah3
  Secretary                Blah4,Blah4
  </code></pre>
  
  <p>This is what I have till now !!</p>
  
  <pre><code>SELECT l.sValue
      , SUBSTRING((
                          SELECT C.sLast + ',' + C.sFirst + ';'
                          FROM contactinfo c
                          inner join contactrole crole
                          ON crole.idcontactinfo = c.idcontactinfo
                          WHERE crole.idcompany = '&lt;&lt;blah&gt;&gt;'
                          and c.idcompany = '&lt;&lt;blah&gt;&gt;'
                          FOR XML PATH('')
  
                      ), 1, 1000000)
  FROM CONTACTROLE CR
  inner join contactinfo c on 
  cr.idcontactinfo = c.idcontactinfo 
  inner join lookupdata l on cr.idlookuprole = l.idlookupdata
  where c.idcompany = '&lt;&lt;blah&gt;&gt;'
  </code></pre>