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