开发者

Group by on date with SQL query?

开发者 https://www.devze.com 2023-01-27 14:50 出处:网络
开发者_开发知识库I have a view IDS.NoDateFrDateTo -------- --------------- 16301/01/201001/01/2010

开发者_开发知识库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

0

精彩评论

暂无评论...
验证码 换一张
取 消