Geek Answers Handbook

Concatenating SQL Server GROUP BY

I have a request similar to this

SELECT J.JobID,T.Title FROM JobsTagMap J Left Join Tags T ON J.TagID=T.TagID

This returns the next data set (simplified, JobID is actually a UniqueIdentifier)

 JobID Title 1 Tag1 1 Tag2 2 Tag2 2 Tag5 2 Tag9

Now I would like to group this by the column JobID and combine the Title, so the results will be as follows:

 JobID Title 1 Tag1,Tag2 2 Tag2,Tag5,Tag9

How can I do it?

+4
source share
2 answers

If you are using SQL Server 2005+. Then you can do the following:

 SELECT JobsTagMap.JobID, STUFF ( ( SELECT ',' +Title FROM Tags WHERE Tags.TagID=JobsTagMap.TagID FOR XML PATH('') ) ,1,1,'') AS Title FROM JobsTagMap

EDIT

Because you did not indicate to us the structure of the table and the data in different tables. It was hard to understand. Therefore, I assume that your table structure looks something like this:

 CREATE TABLE JobsTagMap ( JobID INT, TagID INT ) CREATE TABLE Tags ( TagID INT, Title VARCHAR(100) )

With this data:

 INSERT INTO JobsTagMap VALUES(1,1),(1,2),(2,2),(2,4),(2,5) INSERT INTO Tags VALUES(1,'Tag1'),(2,'Tag2'),(3,'Tag2'),(4,'Tag5'),(5,'Tag9')

If you receive the data that you display, JobID may not be unique. You may have a Job table somewhere where it is unique. If you just want to use this table that you are showing, you need to do something like this:

 ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY JobID) AS RowNbr, JobsTagMap.* FROM JobsTagMap ) SELECT *, STUFF ( ( SELECT ',' +Title FROM Tags JOIN JobsTagMap ON Tags.TagID=JobsTagMap.TagID WHERE JobsTagMap.JobID=CTE.JobID FOR XML PATH('') ) ,1,1,'') AS Title FROM CTE WHERE CTE.RowNbr=1

This will give you this result:

 1 1 1 Tag1,Tag2 1 2 2 Tag2,Tag5,Tag9

So, in the future always show that table structure and data . This will give you the best answers.

+10
source

I use the scalar function for this. There will be some kind of purist who should not use row-based operation, but it works, and if you return only a few lines, the response time is fine.

 CREATE FUNCTION [dbo].[JoinMVText] ( @sID int, @fieldID tinyint ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @MVtextList varchar(max) SELECT @MVtextList = COALESCE(@MVtextList + '; ', '') + docMVtext.value FROM docMVtext with (nolock) WHERE docMVtext.sID = @sID and fieldID = @fieldID RETURN @MVtextList END
+1
source

More articles:


All Articles