views:

858

answers:

2

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

+2  A: 

I'd create a UDF:

create function GetTags(PhotoID int) returns @tags varchar(max)
as
begin
    declare @mytags varchar(max)
    set @mytags = ''

    select @mytags = @mytags + ', ' + tag from tags where photoid = @photoid

    return substring(@mytags, 3, 8000)
end

Then, all you have to do is:

select GetTags(photoID) as tagList from photos
Eric
is that an "inline", "table-valued", or "scalar-valued" function? those are the options VS gives me...
Jason
Scalar Valued -- sorry I didn't specify.
Eric
Elaborating a bit: Table-valued and inline functions both return type table. Those options are to help you with some of the syntax, but if you ran this SQL straight, it would automatically be scalar.
Eric
@eric - thank you so much... with a little bit of modification, i got this to work! now as a bonus, how do i remove the trailing comma (i switched the order around a bit so that the username goes first, then @mytags, otherwise there's a leading comma)?
Jason
@Jason: Sorry, forgot the `substring` when I generalized the function. I had tested it out on my machine first. This will remove the leading comma (quicker than removing a trailing one).
Eric
thanks a ton! it's working now
Jason
+2  A: 

Ok, I feel like I need to jump in to comment about http://stackoverflow.com/questions/1250452/how-do-you-concat-multiple-rows-into-one-column-in-mssql/1250465#1250465 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.

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

For examples of how FOR XML PATH works, consider the following, imagining that you have a table with two fields called 'id' and 'name'

SELECT id, name
FROM table
order by name
FOR XML PATH('item'),root('itemlist')
;

Gives:

<itemlist><item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item></itemlist>

But if you leave out the ROOT, you get something slightly different:

SELECT id, name
FROM table
order by name
FOR XML PATH('item')
;

<item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item>

And if you put an empty PATH string, you get even closer to ordinary string concatenation:

SELECT id, name
FROM table
order by name
FOR XML PATH('')
;

<id>2</id><name>Aardvark</a><id>1</id><name>Zebra</name>

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:

SELECT ',' + name
FROM table
order by name
FOR XML PATH('')
;

,Aardvark,Zebra

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.

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

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

Rob Farley
+1 for effort, but it actually returns XML, which i don't want. for some reason, it changes your original `SELECT ',' + username` to `SELECT TOP (100) PERCENT ', ' + dbo.users.username AS tags`...
Jason
Get rid of "as tags". And don't put it in a function. Run it as is. What did you actually run?
Rob Farley
i can't get rid of the "as" because it puts "as Expr1" as a default, and i didn't run it as a function. i ran it as a standard SQL query :\
Jason
Sounds like you're either using Access or the "Design Query in Editor" function. Put the query in a normal New Query window in Management Studio and run it. You definitely DON'T need the "as Expr1" in there, or the "TOP (100) PERCENT". Get rid of them both.
Rob Farley