title: TSQL how to concatenate horizontally inside the join,
body: <p>I have this task to display CodeName like in sample, and there could be multiple Code separated by commas, I tried to use xml parse but looks like this a bit different situation. In sample code below I did it in simple way but maybe there is better way which be more flexible:
   I also include desired output in photo.
  <a href="https://i.stack.imgur.com/dVTrs.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dVTrs.jpg" alt="Sample of desired output click here"></a></p>
  
  <pre><code>WITH cte1 AS (
  SELECT 'Alpha' Name,  'AA' Code  UNION
  SELECT 'Bravo' Name,  'AA,BB,CC' Code  UNION
  SELECT 'Whiskey' Name,  NULL Code),
   cteLookup AS (
  SELECT 'AA' CodeID,  'Avia' CodeName  UNION
  SELECT 'BB' CodeID,  'Bahamas' CodeName  UNION
  SELECT 'CC' CodeID,  'Chile' CodeName  )
  
  
  SELECT Name,
         Code,
         SUBSTRING(Code,1,2) c1,
         x1.CodeName + ', ' +  ISNULL(x2.CodeName,'') + ', ' +   ISNULL(x3.CodeName,'')  
  FROM Cte1 
  LEFT JOIN cteLookup x1  ON x1.CodeID =  SUBSTRING(Code,1,2)
  LEFT JOIN cteLookup x2  ON x2.CodeID =  SUBSTRING(Code,4,2)
  LEFT JOIN cteLookup x3  ON x3.CodeID =  SUBSTRING(Code,7,2)
  </code></pre>