使用 SQL 从日期范围拆分/透视数据

2022-01-22 00:00:00 pivot sql sql-server

您好，我对拆分/枢轴日期有疑问

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

相关文章