本文介绍了使用 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 从日期范围拆分/透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！