I have to group certain types of data in to one row in the view. I tried below Query:
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
Table data:
| 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
Desired output:
| 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 | |