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 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 this article but I get an error when I try to run the query saying that I can't use 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?)
Best Answer
Ok, I feel like I need to jump in to comment about How do you concat multiple rows into one column in SQL Server? and provide a more preferred answer.
I'm really sorry, but using scalar-valued functions like this will kill performance. Just open SQL Profiler and have a look at what's going on when you use a scalar-function that calls a table.
Also, the "update a variable" technique for concatenation is not encouraged, as that functionality might not continue in future versions.
The preferred way of doing string concatenation to use FOR XML PATH instead.
For examples of how FOR XML PATH works, consider the following, imagining that you have a table with two fields called 'id' and 'name'
Gives:
But if you leave out the ROOT, you get something slightly different:
And if you put an empty PATH string, you get even closer to ordinary string concatenation:
Now comes the really tricky bit... If you name a column starting with an @ sign, it becomes an attribute, and if a column doesn't have a name (or you call it [*]), then it leaves out that tag too:
Now finally, to strip the leading comma, the STUFF command comes in. STUFF(s,x,n,s2) pulls out n characters of s, starting at position x. In their place, it puts s2. So:
SELECT STUFF('abcde',2,3,'123456');
gives:
a123456e
So now have a look at my query above for your taglist.
For each photo, I have a subquery which grabs the tags and concatenates them (in order) with a commma and a space. Then I surround that subquery in a stuff command to strip the leading comma and space.
I apologise for any typos - I haven't actually created the tables on my own machine to test this.
Rob