title: T-SQL - string concatenation
body: <p>Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!)</p>
  
  <p>I have two tables on SQL Server 2005 TABLE 1 and TABLE2:</p>
  
  <p>TABLE1</p>
  
  <pre><code>COL1         COL2
  1            10
  2            20
  3            30
  4            10
  4            20
  5            20
  6            30
  7            10
  7            20
  </code></pre>
  
  <p>TABLE2</p>
  
  <pre><code>COL1         COL2
  10            A
  20            B
  30            C
  </code></pre>
  
  <p>COL2 in TABLE2 is a character representation of the numerical data in COL2 TABLE1. I hope this is understandable?</p>
  
  <p>I have worked out how to select COL1 and COL2 from TABLE1 and concatenate the results to show this:</p>
  
  <pre><code>COL1         COL2Concat
  1            10
  2            20
  3            30
  4            10, 20
  5            20
  6            30
  7            10, 20, 30 
  </code></pre>
  
  <p>Using this:</p>
  
  <pre><code>SELECT  COL1,
          STUFF(( SELECT  ',' + CAST(a.COL2 AS VARCHAR(255)) AS [text()]
                  FROM    TABLE1 a
                  WHERE   a.COL1 = b.COL1
                  ORDER BY a.COL2
                FOR
                  XML PATH('')
                ), 1, 1, '') AS COL2Concat
  FROM    TABLE1 b
  GROUP BY COL1
  ORDER BY COL1
  </code></pre>
  
  <p>But now I'd like to try and get the same result except use the data in COL2 TABLE2... i.e.:</p>
  
  <pre><code>COL1         COL2Concat
  1            A
  2            B
  3            C
  4            A, B
  5            B
  6            C
  7            A, B, C 
  </code></pre>
  
  <p>Any ideas - I'm stuck to be honest as I have tried modifying the STUFF query, but it never seems to come out right...</p>