sql - 如何在 SQL Server 中根据进程类型对数据进行分组？

Question

我必须将某些类型的数据分组到视图中的一行。我试过下面的查询：

select case when Process  in ('A1','A2') then 'A' 
            when Process  in ('C1','C2') then 'C' 
             else Process  as p,max(StartDate),max(EndDate)
 from t
group by case when Process  in ('A1','A2') then 'A' 
            when Process  in ('C1','C2') then 'C' 
             else Process

表数据：

| Process   |    Date   |  Comment  |
+-----------+-----------+-----------+ 
| A1        | 05-06-2018| Submitted |
| A2        | 07-03-2018| Approved  | --Here A1 & A2 are same process
| B1        | 03-02-2018| Pending   |
| C1        | 07-06-2018| Submitted | 
| C2        | 09-25-2018| Pending   | --Here C1 & C2 are same process

期望的输出：

| Process   | StartDate |   EndDate    |   Comments           |
+-----------+-----------+--------------+----------------------+
| A         | 05-06-2018|  07-03-2018  | Submitted ; Approved |
| B         | 03-02-2018|  03-02-2018  | Pending              |
| C         | 07-06-2018|  09-25-2018  | Submitted ; Pending  |                  |

标签： sqlsql-servergroup-bysql-server-2012aggregate-functions