How do you combine multiple rows into a single SQL Server column?

Question

I searched high and low for an answer to this question, but I cannot figure it out. I am relatively new to SQL Server and the syntax is still not enough. I have this data structure (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 |  eleven
 2 1 1 |  2 3
 3 1 1 |  3 2
 4 3 2 |
 5 3 2 |

I am looking for a way to get all the information about a photo (easy), as well as all the tags for this photo, combined, for example, CONCAT(username, ', ') AS Tags , with the last comma removed. I spend time trying to do this. I tried the method in this article , but I get an error when I try to run a query saying that I cannot use DECLARE statements ... do you guys know how to do this? I use VS08 and any DB is installed in it (I usually use MySQL, so I don’t know what taste DB really is ... is this a .mdf file?)

Answer 1

I would create 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 

Answer 2

Well, it seems to me that I need to skip to comment How do you combine multiple rows into a single column on SQL Server? and provide a more preferable answer.

I'm sorry, but using scalar-valued functions like this will lead to performance damage. Just open SQL Profiler and see what happens when you use the scalar function that calls the table.

In addition, the “update variable” method for concatenation is not recommended, as this functionality may not be preserved in future versions.

The preferred way to perform string concatenation is to use the FOR XML PATH.

 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 the 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 ROOT, you will get something a little 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 line, you get even closer to the usual 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 a very complicated bit ... If you name a column starting with the @ sign, it will become an attribute, and if the column does not have a name (or you name it [*]), then it also leaves this tag:

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

Now, finally, to break the leading comma, the STUFF command is turned on. STUFF (s, x, n, s2) extends n characters s, starting at position x. Instead, he puts s2. So:

SELECT STUFF ('abcde', 2,3, '123456');

gives:

a123456e

Now look at my request 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 that captures the tags and combines them (in order) with commma and a space. I then surround this subquery in the stuff command to remove the leading comma and space.

I apologize for any typos - I actually did not create the tables on my machine to check this.

Rob

Answer 3

 Street_Name ; Street_Code 

---

2. west | fourteen
3. east | 7
4. west + east | 714

If you want to show two different rows of concate, how to do it? (I mean the last row that I want to show from the selected result. My table had the first and second record)