SQL数据变换

Question

这是我的源表

Reference ModifiedDate    Teachers Students SchoolID ETC 
------------------------------------------------------------------------- 
1023175  2017-03-03 16:02:01.723  10  25  5 
1023175  2017-03-07 07:59:49.283  15  50  15 
1023175  2017-03-12 11:14:40.230  25  6   5 
1023176  2017-03-04 16:02:01.723  11  35  8 
1023176  2017-03-08 07:59:49.283  16  60  25 
1023177  2017-03-15 11:14:40.230  15  7   2 

我需要下面的输出

Reference StartDate     EndDate 
--------------------------------------------- 
1023175 2017-03-03 16:02:01.723  2017-03-07 07:59:49.283 
1023175 2017-03-07 07:59:49.283  2017-03-12 11:14:40.230 
1023175 2017-03-12 11:14:40.230  9999-12-31 00:00:00.000 
1023176 2017-03-04 16:02:01.723  2017-03-08 07:59:49.283 
1023176 2017-03-08 07:59:49.283  9999-12-31 00:00:00.000 
1023177 2017-03-15 11:14:40.230  9999-12-31 00:00:00.000 (last record should have this value) 

教师学生SchoolID

---

10  25  5 
15  50  15 
25  6   5 
11  35  8 
16  60  25 
15  7   2 

所有其他列像教师，学生和SchoolId等也必须在此与每条记录一起输出。

有关如何实现这一目标的任何建议？ 使用SQL Server 2008

Answer 1

使用outer apply()：

select 
    Reference 
    , StartDate = t.ModifiedDate 
    , EndDate = coalesce(x.ModifiedDate, convert(datetime,'9999-12-31 00:00:00.000')) 
    , Teachers 
    , Students 
    , SchoolID 
from t 
    outer apply (
    select top 1 i.ModifiedDate 
    from t as i 
    where i.Reference = t.Reference 
     and i.ModifiedDate > t.ModifiedDate 
    order by i.ModifiedDate asc 
    ) x 

rextester演示：http://rextester.com/RFTD32624

回报：

+-----------+-------------------------+-------------------------+----------+----------+----------+ 
| Reference |  StartDate  |   EndDate   | Teachers | Students | SchoolID | 
+-----------+-------------------------+-------------------------+----------+----------+----------+ 
| 1023175 | 2017-03-03 16:02:01.723 | 2017-03-07 07:59:49.283 |  10 |  25 |  5 | 
| 1023175 | 2017-03-07 07:59:49.283 | 2017-03-12 11:14:40.230 |  15 |  50 |  15 | 
| 1023175 | 2017-03-12 11:14:40.230 | 9999-12-31 00:00:00.000 |  25 |  6 |  5 | 
| 1023176 | 2017-03-04 16:02:01.723 | 2017-03-08 07:59:49.283 |  11 |  35 |  8 | 
| 1023176 | 2017-03-08 07:59:49.283 | 9999-12-31 00:00:00.000 |  16 |  60 |  25 | 
| 1023177 | 2017-03-15 11:14:40.230 | 9999-12-31 00:00:00.000 |  15 |  7 |  2 | 
+-----------+-------------------------+-------------------------+----------+----------+----------+ 

参考：

• apply() - msdn
• The power of T-SQL's APPLY operator - Rob Farley
• APPLY: It Slices! It Dices! It Does It All! - Brad Shulz