我应该如何修改这条SQL语句？ [英] How should I modify this SQL statement?

问题描述

My 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 
  

返回这些...

  HyperLinksID CatID标签
 1 2运动
 1 2网球
 1 2高尔夫
  

我应该如何修改上面的结果，例如

  HyperLinksID CatID TagsInOneRowSeperatedWithSpaceCharacter 
 1 2 Sport Tennis Golf 
  

更新：正如Brad建议我来到这里...

  DECLARE @TagList varchar（100）
 SELECT @TagList = COALESCE（@TagList +'，'，''）+ CAST（TagID AS nvarc har（100））
 FROM TagsList 
 WHERE HyperLinksID = 1 
 SELECT @TagList 
  

现在结果如下所示：



  HyperLinksID CatID TagsInOneRowSeperatedWithSpaceCharacter 
 1 2 ID_OF_Sport ID_OF_Tennis ID_OF_Golf 
  

当然，我必须将来自的内容< TagList 变量和原始 SELECT 语句...

<这意味着我将不得不等待神圣的SO赏金：（

）

连结行值

如果您想尝试使用CLR代码，那么还有一些为MS SQL创建用于并置的自定义聚合函数的示例。

这篇文章非常详尽，有很多方法可以实现您的目标。

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 the above to have results 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 holy SO bounty :(

解决方案

If SQL, try this post:

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.

这篇关于我应该如何修改这条SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！