Group by on date with SQL query?

开发者_开发知识库I have a view

ID   S.No  DateFr        DateTo
---  ----- ------       ---------
1    63    01/01/2010    01/01/2010
1    63    01/01/2010    01/11/2010
2    64    01/01/2010    01/01/2010 
2    64    01/01/2010    01/11/2010
3    65    01/01/2010    01/01/2010
3    65    01/01/2010    01/11/2010

Now I want results like

ID   S.No  DateFr       DateTo
1     63   01/01/2010   01/11/2010
2     64   01/01/2010   01/11/2010
3     65   01/01/2010   01/11/2010

Note Only DateFr is changed in records so I want it to shift from being a row to column. The ID is not fixed, it can range to an unlimited no like 1,1,1 ------ 30,30,30. I am using DB2 with ISeries.

---

It should be as simple as something like this:

select id, s.no, min(datefr), max(dateto) from your_table group by id, s.no

---

SELECT ID,S.No,dateFr,DateTo  FROM your_table GROUP BY ID,S.No,dateFr,DateTo 

---

Use Max(), Case When and Group By.

In my opinion, this technique was called "Pivot" or "Crosstab".

---

You may want to use SQL Server PIVOT command.

Check this out