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

I have a table of data:

我有一张数据表:

|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|

I want to aggregate this string data by key - like in a group but I can't work out how. What I want to end up with is:

我想按key - like在一个组中聚合这个字符串数据，但是我不知道怎么做。最后我想说的是

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

Better still would be to end up with the sequence position inserted into the aggregation where the order of the sequence is determined by the order of the corresponding date field.

更好的方法是将序列位置插入到聚合中，其中序列的顺序由相应日期字段的顺序决定。

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

This seems like something I could do with a Common Table Expression, but I don't seem to be able to work out how.

这似乎是我可以用一个通用的表表达式做的事情，但是我似乎不能算出怎么做。

I wouldn't ordinarily perform this kind of operation in SQL - I'd normally pull the data into an app and manipulate it as necessary in code.

我通常不会在SQL中执行这种操作——我通常会将数据拖放到应用程序中，并在必要时在代码中对其进行操作。

Unfortunately this does not appear to be an option in this instance.

不幸的是，在这个实例中，这似乎不是一个选项。

How can I do this in SQL Server 2005 T-SQL.

如何在SQL Server 2005 T-SQL中实现这一点。

Is a CTE the right approach? Is there something more appropriate?

CTE是正确的方法吗?有什么更合适的吗?

1 个解决方案

#1

DECLARE @t TABLE([Key] CHAR(1), [Field] VARCHAR(32), DateField DATETIME);

INSERT @t 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';

;WITH x AS 
(
  SELECT [Key], [Field], rn = ROW_NUMBER() OVER
  (PARTITION BY [Key] ORDER BY [DateField])
  FROM @t
)
SELECT [Key], [Field Aggregate] = STUFF(
  (SELECT ', ' + CONVERT(VARCHAR(12), rn) + ': ' + [Field]
    FROM x AS x2 WHERE x2.[Key] = x.[Key]
    ORDER BY rn
    FOR XML PATH('')
  ), 1, 2, '')
FROM x 
GROUP BY [Key]
ORDER BY [Key];

Results:

结果:

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

P.S. if your values in Field could contain characters that are problematic for XML (e.g. &, >, < etc.) you'll want to change this line:

附注:如果字段中的值可能包含对XML有问题的字符(例如&、>、 <等)，则需要更改这一行:< p>

), 1, 2, '')

To this:

, TYPE).value('.', 'nvarchar(max)'), 1, 2, '')

(This will prevent entitization.)

(这将防止entitization。)

#1

DECLARE @t TABLE([Key] CHAR(1), [Field] VARCHAR(32), DateField DATETIME);

INSERT @t 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';

;WITH x AS 
(
  SELECT [Key], [Field], rn = ROW_NUMBER() OVER
  (PARTITION BY [Key] ORDER BY [DateField])
  FROM @t
)
SELECT [Key], [Field Aggregate] = STUFF(
  (SELECT ', ' + CONVERT(VARCHAR(12), rn) + ': ' + [Field]
    FROM x AS x2 WHERE x2.[Key] = x.[Key]
    ORDER BY rn
    FOR XML PATH('')
  ), 1, 2, '')
FROM x 
GROUP BY [Key]
ORDER BY [Key];