使用SQL从日期范围分割/透视数据 [英] Splitting/Pivot Data from a Date Range using SQL

问题描述

我对分组/枢纽日期有疑问

Hi I have a problem with regarding on split/pivot dates

这是我的查询

select Name
     , Start
     , End 
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'

样本数据就是这样

And Sample Data would be like this

我要做的是像这样

What I want to do is to split/pivot all data by date range like this

但是我不知道该怎么办，如果可能的话?
谢谢.

But I don't know how can i do this and if it is possible?
Thank you.

推荐答案

，您需要生成所有日期.我在这里通过使用CTE.然后，我将这个日期范围与您的数据一起加入，并获得所需的结果.

you need to generate all the dates. i did this here by using a cte. i then join this date range with your data and receive the sought result.

with DateRange AS
(
    SELECT CAST('1/27/2014' as DATEtime) DateValue
     UNION ALL
    SELECT dateadd(dd,1,DateValue)
      FROM DateRange
     WHERE dateadd(dd,1,DateValue) <= CAST('3/31/2014' as datetime)
)
select name
     , DateValue
  from Employees
  join DateRange
    on start <= DateValue
   and [end] >= datevalue
 order by 
       name
     , DateValue

---

更新问题后已过时 我会通过一个简单的工会来解决这个问题:

---

outdated after your updated question i would go about it with a simple union:

select Name
     , Start
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'
 union all
select Name
     , End
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'
 order by
       Name

这篇关于使用SQL从日期范围分割/透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！