How should i modify this SQL statement?

Question

My SQL Server view

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 should I modify above to have result like

HyperLinksID CatID TagsInOneRowSeperatedWithSpaceCharacter
1            2     Sport Tennis Golf

UPDATE: As Brad suggested i came up 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 have to combine the contents FROM the @TagList variable and the ORIGINAL SELECT statement...

Which means that i ll have to wait for the holly SO boundy :(

Answer 1

If SQL, try this post:

http://stackoverflow.com/questions/2064940/concatenating-row-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 exhaustive with lots of ways to accomplish your goal.

Answer 2

Using the approach from here to avoid any issues 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've removed the manually added spaces. For delimiters other than spaces such as commas Peter's answer seems more appropriate.

If you know your data will not contain any problematic characters then a simpler (probably more performant) version is

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

Answer 3

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() gets around most common problems w/ special characters in XML.