将SQL行聚合到单个列中

I would like to aggregate results of a column into a single row using MS SQL.

我想使用MS SQL将列的结果聚合到一行中。

I have create a sql fiddle which showcases the issue:

我创建了一个sql小提琴,它展示了这个问题:

http://sqlfiddle.com/#!3/384d5/2

Basically I would like all 'Tag' results on the same line, grouped by the different Tags

基本上我希望所有'Tag'结果在同一行,按不同的标签分组

So in the example results above, I would expect the results to be like so:

所以在上面的示例结果中,我希望结果如下:

Id | Tag      | OptionText
1  | IsMobile | Yes
1  | Mission  | I am buying this, I am using this

The closest I have got is by using a STUFF function but I can't seem to get it correctly as it outputs all the OptionText values on each row:

我最接近的是使用STUFF函数,但我似乎无法正确获取它,因为它输出每行上的所有OptionText值:

select Id, Tag, STUFF(CAST((
    SELECT [text()] = ', ' + [OptionText]
    FROM testing
    FOR XML PATH(''), TYPE) AS
VARCHAR(max)), 1, 2, '')  OptionText
  from testing
  group by Id, Tag

Here are the results for that query: http://sqlfiddle.com/#!3/384d5/5

以下是该查询的结果:http://sqlfiddle.com/#!3 / 384d5 / 5

1 个解决方案

#1

That'd be your query:

这是你的疑问:

DECLARE @testing TABLE
(
  Id INT
  , Tag NVARCHAR(250)
  , OptionText NVARCHAR(250)
);

INSERT INTO @testing
VALUES (1, 'IsMobile', 'Yes')
  , (1, 'Mission', 'I am using this')
  , (1, 'Mission', 'I am buying this');

SELECT T.Id, T.Tag, STUFF(Temp.Concatenated, 1, 1, '') AS Concatenated
FROM (
  SELECT DISTINCT Id, Tag
  FROM @testing
  ) AS T
CROSS APPLY (
  SELECT DISTINCT ',' + OptionText AS [text()]
  FROM @testing AS TT
  WHERE TT.Id = T.Id
    AND TT.Tag = T.Tag
  FOR XML PATH('')
  ) AS Temp(Concatenated);

You'll have to query your table twice, unfortunately. Once - to get unique Ids with their tags and second time to get OptionText. STUFF will remove first comma, that was added during concatenation.

不幸的是,你必须两次查询你的表。一次 - 用标签获取唯一的ID,第二次获得OptionText。 STUFF将删除在连接期间添加的第一个逗号。

Here's a SQL Fiddle: http://sqlfiddle.com/#!3/5a0079/1

这是一个SQL小提琴:http://sqlfiddle.com/#!3/5a0079/1

Result:

╔════╦══════════╦══════════════════════════════════╗
║ Id ║   Tag    ║           Concatenated           ║
╠════╬══════════╬══════════════════════════════════╣
║  1 ║ IsMobile ║ Yes                              ║
║  1 ║ Mission  ║ I am buying this,I am using this ║
╚════╩══════════╩══════════════════════════════════╝