title: How do you concat multiple rows into one column in SQL Server?
body: <p>I've searched high and low for the answer to this, but I can't figure it out. I'm relatively new to SQL Server and don't quite have the syntax down yet. I have this datastructure (simplified):</p>
  
  <pre>
  Table "Users"         | Table "Tags":
  UserID    UserName    | TagID    UserID    PhotoID
  1         Bob         | 1        1         1
  2         Bill        | 2        2         1
  3         Jane        | 3        3         1
  4         Sam         | 4        2         2
  -----------------------------------------------------
  Table "Photos":              | Table "Albums":
  PhotoID   UserID    AlbumID  | AlbumID     UserID
  1         1         1        | 1           1
  2         1         1        | 2           3
  3         1         1        | 3           2
  4         3         2        |
  5         3         2        |
  </pre>
  
  <p>I'm looking for a way to get the all the photo info (easy) plus all the tags for that photo concatenated like <code>CONCAT(username, ', ') AS Tags</code> of course with the last comma removed. I'm having a bear of a time trying to do this. I've tried the method in <a href="http://mosisa.wordpress.com/2008/03/20/howto-simply-concatenate-multiple-rows-of-data-into-a-variable-in-sql-server-2005/" rel="nofollow noreferrer">this article</a> but I get an error when I try to run the query saying that I can't use <code>DECLARE</code> statements... do you guys have any idea how this can be done? I'm using VS08 and whatever DB is installed in it (I normally use MySQL so I don't know what flavor of DB this really is... it's an .mdf file?)</p>