生成一段日期范围并与另一个日期范围进行比较，并将其透视。

Question

我有这样一个情况，需要生成一段日期范围（比如起始日期和结束日期）。现在我想在这些生成的日期上填充特定日期的记录。

;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM   DateRange
    WHERE  DateValue + 1 <= @end_date
)

如果@start_date ='2013-01-01' and @end_date= '2013-01-05'，生成的日期将是'01/01/2013,01/02/2013,01/03/2013,01/04/2013,01/05/2013'。现在我想将其旋转以填充特定日期的特定数据。如何实现？

编辑：我有三列，分别是持续时间、开始日期和结束日期。现在我想动态生成这些日期，并相应地填充持续时间的值。

例如：如果Duration = 6并且@start_date ='2013-01-01' and @end_date= '2013-01-05'，我想生成一个持续时间为6的日期列表。

更新2：

如果您没有理解我的问题，这里是完整的详细信息。

1. 我必须传递@startDate和@endDate作为参数
2. 我在表中有Id、StartDate和EndDate、duration和其他字段
3. 现在当我传递参数时，查询应该从@startDate和@endDate生成日期范围
4. 由于日期已经生成，它必须检查表中的StartDate和EndDate日期，并将Duration的值设置为从StartDate到EndDate的那些字段。

所需输出： 如果@startDate='2013-01-01'并且@endDate='2013-01-07'，则输出必须如下所示： 注意：第一张图片中两行的Id相同。

更新3：

- Suraj Shrestha

---

将数据旋转为动态数量的列需要使用动态SQL。在StackOverflow上有许多这样的问题和答案，这是我找到的第一个...可能是SQL Server中的动态枢轴的重复。 - MatBailie

@sna2stha - 这是正确的吗，这就是你要找的吗？你想让日期成为“列”吗？请注意，如果您只需要特定数量（例如，最近30天），则不必动态获取：只需获取当前日期-1等（现在，“标签”将不匹配，但如果您有一个不同的语言实际上正在进行前端报告，那通常可以处理）。 - Clockwork-Muse

这不是我要问的。 "Pivoting" 是 SQL/Data 术语中的一个特定术语 - 简单地说，它是关于交换行和列（这经常用于图表/报告目的）。生成一系列日期作为行是微不足道的；那么，您想让这些日期显示为列吗？请注意，所有 RDBMS 都有一些允许的列数上限。虽然通常人类可以处理的范围是有限的... - Clockwork-Muse

@Clockwork-Muse：我正在处理报告，所以我需要长达一年的时间。 - Suraj Shrestha

在这种情况下，我不会使用 SQL 进行处理，而是在应用程序代码层面进行处理。 - Allan S. Hansen

Answer 1

[更新于2013年12月18日11:22 UTC]
[更新于2013年12月18日14:19 UTC]
[更新于2013年12月19日11:11 UTC]

由于我不知道您使用的表名，所以我创建了一个名为yeahyeah的表。您应该将其替换为自己表的名称。我插入了您在问题中提到的值（fromdate、todate和duration）。

存储过程应该像这样。我称之为pivotit。

create procedure pivotit (@start_date date, @end_date date)
as

/*
step 1:
identify all the data you need to fill the pivoted table.
this is achieved by using your daterange generator and joining
it with your table. the result is kept in a dynamic table
*/

declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT dateadd(dd,1,DateValue)
    FROM   DateRange
    WHERE  dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on (    DateRange.DateValue >= yeahyeah.FromDate 
                  and DateRange.DateValue <= yeahyeah.ToDate);

/* 
step 2:
for pivot you need all the columns that will be adressed. so
we create a string with all the distinct dates from the dynamic 
table. these will then be put into a format like [1], [2], [3], ...
to create a dynamic select.
*/


declare @p varchar(max) = '';
declare @s varchar(max);

select @p = @p + ', [' + CONVERT(varchar,d) + ']' from (select distinct d from @acols) a;
set @p = SUBSTRING(@p,3,len(@p)-2);

/*
step 3:
create the dynamic select. 
alas neither the dynamic table nor the parameters are available from 
inside the dynamic sql. i might try to use bind variables, but was 
not 100% sure if that would work here. so put in the declares for start_ 
and end_date from the procedure parameters and build up the dynamic table 
once more.

then i use @p for the pivoted select. this is done by selecting the column
for the rows (id) and all the values from the pivot as columns (@p).

details on the whole pivot thing are here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
basically you tell sql-server (explicitly) what you want as the columns (@p),
what rows you want (id) and how to aggregate the values in the intersections
(sum(l))

[update 2013-12-19]
i added a routine that makes a cartesian product that has all the combination
of dates and ids in @acols, finds the ones that are missing in @acols and inserts
them with duration 0. then the pivoted cells are complete and display the zero
instead of NULL. you cannot use isnull or coalesce here since not the value
of the cell is NULL, the intersection simply did not exist.

*/

set @s = '
declare @start_date date = convert(date,'''+CONVERT(varchar,@start_date,112)+''',112);
declare @end_date date = convert(date,'''+CONVERT(varchar,@end_date,112)+''',112);
declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT dateadd(dd,1,DateValue)
    FROM   DateRange
    WHERE  dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on (    DateRange.DateValue >= yeahyeah.FromDate 
                  and DateRange.DateValue <= yeahyeah.ToDate);

with cart as 
(
     select distinct 
            a.i
          , b.d 
       from @acols a 
       join @acols b 
         on 1=1
)
insert into @acols (i, d, l)
select cart.i
     , cart.d
     , 0
  from cart 
  left outer join
       @acols a
    on cart.i = a.i
   and cart.d = a.d
 where a.i is null;

select id, '+@p+'
       from
( select convert(varchar,d) as d
       , l
       , i as id
    from @acols ) as sourcetable
  pivot ( 
    sum(l)
    for d in ('+@p+')
    )  as pivottable';

execute(@s);

创建过程后，您可以执行以下操作：

exec pivotit @start_date = '2013-01-01', @end_date = '2013-01-31'

然后会产生以下结果：

- Brett Schneider

---

在第30行，数据透视表显示列“2013-08-01”被多次指定。 - Suraj Shrestha

在您的表中，'2013-08-01'有两个持续时间。当同一天有两个持续时间时，您想要做什么？ - Brett Schneider

这段程序相关的内容翻译成中文：两个日期意味着同一日期有两个不同的ID。而这个带有不同ID的日期具有不同的时间长度，所以我想要在同一日期的下一行填充这个不同的时间长度。 - Suraj Shrestha

谢谢你的回答。我已经寻找了三天。你帮我节省了剩余时间。再次感谢你。你能否用简单的步骤让我理解这段代码的工作原理呢？ - Suraj Shrestha

我需要问一个最后的问题，将那些空值替换为零。我尝试使用isnull(l, 0)作为l，但没有转换成功。 - Suraj Shrestha

Answer 2

您可以按照以下方式编写查询语句：

declare @start_date datetime,@end_date datetime ;
set @start_date ='2013-01-01' ;
set @end_date = '2013-01-05' ;
DECLARE @columns NVARCHAR(MAX),@sql NVARCHAR(MAX);
SET @columns = N'';

WITH DateRange AS
(
    SELECT @start_date DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM   DateRange
    WHERE  DateValue + 1 <= @end_date
)
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(SpreadCol)
FROM (select distinct convert(varchar(10),DateValue,101) as SpreadCol 
from DateRange
) AS T;  

PRINT @columns;

然后在动态Pivot查询中使用@columns作为扩展列。

- Deepshikha

---

谢谢您的回答，但我需要的答案不是您提供的。请查看我的更新。 - Suraj Shrestha

Answer 3

使用SQL Server Reporting Services向导。

在向导中，将查询设置为日期范围表与数据表的左连接。

作为报告类型选择矩阵。

将日期分配到列区域，将数据表的行ID分配到行区域，将数据表的值列分配到值区域。

选择一个样式。

生成报告。

如果需要定期生成报告，可以将其发布到报告服务器上。用户可以导出到首选格式。它看起来也很漂亮 ;)

如果您喜欢，还可以将结果绘制成图形。

有关报告向导的更多信息，请参见http://technet.microsoft.com/en-us/library/ms160326(v=sql.105).aspx。

有关生成日期范围表的替代方法，请参见SQL query to select dates between two dates。

- flup