Geek Answers Handbook

How do I modify this SQL statement?

My view of SQL Server

SELECT geo.HyperLinks.CatID, geo.Tags.Tag, geo.HyperLinks.HyperLinksID FROM geo.HyperLinks LEFT OUTER JOIN geo.Tags INNER JOIN geo.TagsList ON geo.Tags.TagID = geo.TagsList.TagID ON geo.HyperLinks.HyperLinksID = geo.TagsList.HyperLinksID WHERE HyperLinksID = 1

returns these ...

 HyperLinksID CatID Tags 1 2 Sport 1 2 Tennis 1 2 Golf

How do I modify the above to have results like

 HyperLinksID CatID TagsInOneRowSeperatedWithSpaceCharacter 1 2 Sport Tennis Golf

UPDATE: As Brad suggested, I came here ...

 DECLARE @TagList varchar(100) SELECT @TagList = COALESCE(@TagList + ', ', '') + CAST(TagID AS nvarchar(100)) FROM TagsList WHERE HyperLinksID = 1 SELECT @TagList

Now the result looks like

 HyperLinksID CatID TagsInOneRowSeperatedWithSpaceCharacter 1 2 ID_OF_Sport ID_OF_Tennis ID_OF_Golf

And of course, I need to combine the contents from the @TagList variable and the original SELECT ...

This means that I will have to wait for holy SO generosity: (

+4
source share
3 answers

If SQL, try this post:

Concatenation of string values

If you want to try your hand at CLR code, there are examples of creating a custom aggregate function for concatenation, again for MS SQL.

This post is pretty comprehensive with many ways to achieve your goal.

+11
source

Using the approach from here to avoid any problems if your tag names contain special XML characters :.

 ;With HyperLinks As ( SELECT 1 AS HyperLinksID, 2 AS CatID ), TagsList AS ( SELECT 1 AS TagId, 1 AS HyperLinksID UNION ALL SELECT 2 AS TagId, 1 AS HyperLinksID UNION ALL SELECT 3 AS TagId, 1 AS HyperLinksID ) , Tags AS ( SELECT 1 AS TagId, 'Sport' as Tag UNION ALL SELECT 2 AS TagId, 'Tennis' as Tag UNION ALL SELECT 3 AS TagId, 'Golf' as Tag ) SELECT HyperLinksID, CatID , (SELECT mydata FROM ( SELECT Tag AS [data()] FROM Tags t JOIN TagsList tl ON t.TagId = tl.TagId WHERE tl.HyperLinksID = h.HyperLinksID ORDER BY t.TagId FOR XML PATH(''), TYPE ) AS d ( mydata ) FOR XML RAW, TYPE ) .value( '/row[1]/mydata[1]', 'varchar(max)' ) TagsInOneRowSeperatedWithSpaceCharacter FROM HyperLinks h

Edit: As KM points out in the comments, this method actually automatically adds spaces, so I removed manually added spaces. For delimiters other than spaces, such as commas, Peter's answer seems more appropriate.

If you know that your data will not contain any problematic characters, then a simpler (possibly more efficient) version

 SELECT CatID , HyperLinksID, stuff( ( SELECT ' ' + Tag FROM Tags t JOIN TagsList tl ON t.TagId = tl.TagId WHERE tl.HyperLinksID = h.HyperLinksID ORDER BY t.TagId FOR XML PATH('') ), 1, 1, '') TagsInOneRowSeperatedWithSpaceCharacter FROM HyperLinks h
+4
source

Use FOR XML in a correlated subquery. For a space-delimited list:

 SELECT h.HyperLinksID, h.CatID , TagList = ( SELECT t.Tag AS [data()] FROM geo.TagList l JOIN geo.Tags t ON l.TagId = t.TagId WHERE l.HyperLinksID = h.HyperLinksID ORDER BY t.Tag FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)') FROM geo.HyperLinks AS h WHERE h.HyperLinksID = 1

For any other delimiter:

 SELECT h.HyperLinksID, h.CatID , TagList = STUFF(( SELECT ', '+t.Tag FROM geo.TagList l JOIN geo.Tags t ON l.TagId = t.TagId WHERE l.HyperLinksID = h.HyperLinksID ORDER BY t.Tag FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)') ,1,2,'') FROM geo.HyperLinks AS h WHERE h.HyperLinksID = 1

The subquery creates a delimited list, and then STUFF(...,1,2,'') removes the leading,. TYPE).value() bypasses most common XML special character issues.

+2
source

More articles:


All Articles