sql-server - 如何在 T-SQL 中聚合字符串数据

Question

我有一个数据表:

|Key|Field |DateField |
|A  |Value1|2012-06-01|
|A  |Value2|2012-06-02|
|A  |Value3|2012-06-03|
|B  |Value4|2012-06-04|
|B  |Value5|2012-06-05|

我想按键聚合这个字符串数据 - 就像在一个组中一样，但我不知道如何。
我想要结束的是:

|Key|Field Aggregate       |
|A  |Value1, Value2, Value3|
|B  |Value4, Value5        |

更好的是最终将序列位置插入聚合中，其中序列的顺序由相应日期字段的顺序确定。

|Key|Field Aggregate                |
|A  |1: Value1, 2: Value2, 3: Value3|
|B  |1: Value4, 2: Value5           |

这似乎是我可以用公用表表达式做的事情，但我似乎无法弄清楚如何做。

我通常不会在 SQL 中执行这种操作——我通常会将数据拉入应用程序并在代码中根据需要对其进行操作。

不幸的是，在这种情况下，这似乎不是一个选项。

如何在 SQL Server 2005 T-SQL 中执行此操作。

CTE 是正确的方法吗？有更合适的吗？

Answer 1

鉴于此示例数据:

CREATE TABLE #t
(
  [Key]     char(1),
  [Field]   varchar(32), 
  DateField datetime
);

INSERT #t([Key],[Field],DateField) 
  SELECT 'A','Value1','20120601'
  UNION ALL SELECT 'A','Value2','20120602'
  UNION ALL SELECT 'A','Value3','20120603'
  UNION ALL SELECT 'B','Value4','20120604'
  UNION ALL SELECT 'B','Value5','20120605';

在 SQL Server 2017 之前的 SQL Server 版本中，我们可以这样做:

SELECT [Key], [Field Aggregate] = STUFF(
  (SELECT ', ' + CONVERT(varchar(11), 
      ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY DateField)) 
      + ': ' + [Field]
    FROM #t AS t2 WHERE t2.[Key] = t.[Key]
    ORDER BY DateField
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 2, '')
  FROM #t AS t
  GROUP BY [Key] ORDER BY [Key];

结果:


key
现场聚合


一个
1:值 1、2:值 2、3:值 3

乙
1:值 4，2:值 5

示例 db<>fiddle

在 SQL Server 2017 或更高版本中，我们可以更轻松地使用 STRING_AGG() :

;WITH cte AS
(
  SELECT [Key], [Field], rn = ROW_NUMBER() OVER 
    (PARTITION BY [Key] ORDER BY DateField)
    FROM #t
)
SELECT [Key], [Field Aggregate] = STRING_AGG(
    CONCAT(rn, ': ', [Field]), ', ')
    WITHIN GROUP (ORDER BY rn) 
  FROM cte
  GROUP BY [Key] ORDER BY [Key];

示例 db<>fiddle