SQL-可能存在透视问题

Question

我有一个具有以下结构的表

Item Id, Start Date, End Date  
1      , 2015-01-01, 2015-06-01  
2      , 2015-01-01, 2015-02-01   
3      , 2015-03-01, 2015-08-01  
4      , 2015-06-01, 2015-10-01

我想查看结果，这样我每个月都会在列中有。

每一行都将包含本月内项目的id。

示例：我要求2015-01-01到2015-03-01内的所有项目。

结果应以列形式显示该范围内的所有月份。所以在这种情况下，它是3列，Jan、Feb和March。

行数将是该范围内的项目总数，但只有当项目在范围内时，每个单元格才应显示项目id的值：

示例：

2015-01-01, 2015-02-01, 2015-03-01   
    1           1          1  
    2           2         NULL  
   NULL        NULL        3

Answer 1

为了使用pivot，您可以创建一个递归cte，获取每个项目id及其所涵盖的月份列表，然后对cte进行pivot。

;WITH cte AS 
(
    SELECT [Item Id], [Start Date], [End Date] 
    FROM   Table1
    WHERE  [Start Date] BETWEEN '2015-01-01' AND '2015-03-01' --Date Range you want
           OR [End Date] BETWEEN '2015-01-01' AND '2015-03-01' --Date Range you want
    UNION  ALL
    SELECT [Item Id], DATEADD(MONTH, 1, [Start Date]), [End Date]
    FROM   cte
    WHERE  DATEADD(MONTH, 1, [Start Date]) <= [End Date]
)
SELECT [2015-01-01],[2015-02-01],[2015-03-01] --List of Dates you want
FROM (
    SELECT [Item Id] rn, -- need a unique id here to give one row per record
           [Item Id], 
           CONVERT(VARCHAR(10), [Start Date], 120) [Start Date] -- Format date to yyyy-mm-dd
    FROM   cte
) t
PIVOT 
( MAX([Item Id]) 
    FOR [Start Date] IN ([2015-01-01],[2015-02-01],[2015-03-01])
) p

Answer 2

您很可能需要使用动态SQL。

这是您的数据：

declare @first date = '20150101';
declare @last date = '20150301';
Create Table #items(ItemId int, StartDate date, EndDate date);
Insert into #items(ItemId, StartDate, EndDate) values
(1, '2015-01-01', '2015-06-01')  
, (2, '2015-01-01', '2015-02-01')   
, (3, '2015-03-01', '2015-08-01')  
, (4, '2015-06-01', '2015-10-01');

您首先需要获得值和列的范围：

declare @values varchar(max);
declare @cols varchar(max);
with range(d) as (
    Select top(DATEDIFF(month, @first, @last)+1) cast(DATEADD(month, ROW_NUMBER() over(order by (select 0))-1, @first) as varchar(20))
    From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
    ) as x(n)
)
Select @values = coalesce(''+@values+ ', ', ' ') + '('''+d+''')' 
    , @cols = coalesce(''+@cols+ ', ', ' ') + '['+left(DATENAME(month, d), 3)+CAST(year(d) as char(4))+']' 
From range
;

这基本上为@first和@last之间的每个日期创建一行，并用括号和逗号（@values）或括号（@cols）连接它们。

@values和@cols中的内容如下：

 @values = ('2015-01-01'), ('2015-02-01'), ('2015-03-01')
 @cols = [Jan2015], [Feb2015], [Mar2015]

然后使用以下两个变量创建一个SQL脚本：

declare @sql nvarchar(max);
Set @sql = '
Select * 
From (
    Select i.ItemId, d = left(DATENAME(month, r.d), 3)+CAST(year(r.d) as char(4))
        , id = case when r.d >= i.StartDate and r.d <= i.EndDate then i.ItemId end
    From (values'+@values+') as r(d) 
    Cross Join (Select ItemId, StartDate, EndDate From #items 
        Where (@first >= StartDate and @first <= EndDate) or (@last >= StartDate and @last <= EndDate)
    ) i 
) as dates
Pivot (
    min(id)
    For d in('+@cols+')
) as piv
';

这是数据透视查询。

创建的SQL在本例中如下所示：

Select * 
From (
    Select i.ItemId, d = left(DATENAME(month, r.d), 3)+CAST(year(r.d) as char(4))
        , id = case when r.d >= i.StartDate and r.d <= i.EndDate then i.ItemId end
    From (values ('2015-01-01'), ('2015-02-01'), ('2015-03-01')) as r(d) 
    Cross Join (Select ItemId, StartDate, EndDate From #items 
        Where (@first >= StartDate and @first <= EndDate) or (@last >= StartDate and @last <= EndDate)
    ) i 
) as dates
Pivot (
    min(id)
    For d in( [Jan2015], [Feb2015], [Mar2015])
) as piv

您最终可以执行脚本：

exec sp_executesql @sql, N'@first date, @last date', @first, @last;

输出：

ItemId  Jan2015 Feb2015 Mar2015
1       1       1       1
2       2       2       NULL
3       NULL    NULL    3

Answer 3

可能类似于。。。。

Select 
   CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 1 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-01-01]
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 2 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-02-01]   
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 3 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-03-01]   
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 4 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-04-01]
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 5 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-05-01]
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 6 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-06-01]
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 7 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-07-01]
  ,CASE WHEN EXISTS (SELECT 1 FROM  TableName where Month(Start) = 8 AND ItemId = t.ItemId) THEN t.ItemId END AS [2015-08-01]
  ,..... and so on..... for all the other months...
from TableName t