sql - 如何获取记录中的所有行？

Question

如何获取一行中发生在一个人身上的所有事件？

例子:(没有任何意义，只是举个例子)

Id Name   Event    Date
1  John   Running  2017/09/13
2  Mary   Sleeping 2017/08/21
3  Karl   Sleeping 2017/07/30
1  John   Tired    2017/10/16
2  Mary   Playing  2017/10/16

我想得到如下结果:

Id Name   Event              Date
1  John   Running, Tired     2017/10/16
2  Mary   Sleeping, Playing  2017/10/16
3  Karl   Sleeping           2017/07/30

事件记录按事件日期升序显示。

最后它应该返回所有这些事件行的最大日期

Answer 1

SELECT DISTINCT B.id, Name, MAX(Date) Date,
    substring(
        (
            SELECT ','+A.Event  AS [text()]
            FROM yourtable A
            WHERE A.Id = B.Id
            ORDER BY A.Id
            For XML PATH ('')
        ), 2, 1000) Event
FROM yourtable B
GROUP BY B.Id, Name

输出

id  Name    Date                    Event
1   John    2017-10-16T00:00:00Z    Running,Tired
3   Karl    2017-07-30T00:00:00Z    Sleeping
2   Mary    2017-10-16T00:00:00Z    Sleeping,Playing

SQL fiddle :http://sqlfiddle.com/#!6/2fd81/13/0