How do you concat multiple rows into one column in SQL Server?

admin

Administrator
Staff member
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):


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 |


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
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...s-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
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?)