USE adventureworks; 
GO 


-- first query showing columns  
SELECT c.firstname + ' ' + c.lastname 
  FROM  [AdventureWorks].[HumanResources].[Employee] e 
  INNER JOIN [AdventureWorks].[Person].[Contact] c 
          ON c.contactid = e.contactid 
  ORDER BY c.lastname; 
 GO 
   
-- second query as csv  
SELECT Substring((SELECT ', ' + c.firstname + ' ' + c.lastname 
  FROM  [AdventureWorks].[HumanResources].[Employee] e 
  INNER JOIN [AdventureWorks].[Person].[Contact] c 
          ON c.contactid = e.contactid 
  ORDER BY c.lastname 
FOR XML PATH('')),3,10000000) AS list; 



   
   
   
   
  -- first query showing columns  
SELECT e.title, c.firstname + ' ' + c.lastname 
  FROM  [AdventureWorks].[HumanResources].[Employee] e 
  INNER JOIN [AdventureWorks].[Person].[Contact] c 
          ON c.contactid = e.contactid 
  ORDER BY e.title; 



-- Now with the grouping by title, and a csv list for each title
SELECT e.title, 

( SELECT Substring((SELECT ', ' + c1.firstname + ' ' + c1.lastname 
  FROM  [AdventureWorks].[HumanResources].[Employee] e1 
  INNER JOIN [AdventureWorks].[Person].[Contact] c1 
          ON c1.contactid = e1.contactid 
  WHERE e1.title = e.title 
  ORDER BY c1.lastname 
FOR XML PATH('')),3,10000000) AS list ) AS employeelist 

  FROM  [AdventureWorks].[HumanResources].[Employee] e 
  GROUP BY e.title;