基于日期的动态列

您可以查询如下：

select * from #DiscoverData d
cross apply 
( Select top (datediff(mm,d.firstdiscovered, d.lastdiscovered) +1) 
    RowN = Right(Convert(varchar(10), Dateadd(mm, Row_Number() over(order by (Select NULL))-1 , d.FirstDiscovered),6),6)        
    from master..spt_values s1, master..spt_values s2 )  c
pivot (Count(RowN) for RowN in ([Apr 17],[May 17],[Jun 17],[Jul 17], [Aug 17])) p

对于月份的动态列表，您可以使用如下查询：

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((
    Select ','+QuoteName(RowN) from ( Select min(firstdiscovered) as firstdiscovered, max(lastdiscovered) as lastdiscovered from #DiscoverData ) d
    cross apply
    ( Select top (datediff(mm,d.firstdiscovered, d.lastdiscovered) +1) 
        RowN = Right(Convert(varchar(10), Dateadd(mm, Row_Number() over(order by (Select NULL))-1 , d.FirstDiscovered),6),6)        
        from master..spt_values s1, master..spt_values s2 )  c
    for xml path('')),1,1,'')

Set @query = ' Select * from #DiscoverData d
    cross apply 
    ( Select top (datediff(mm,d.firstdiscovered, d.lastdiscovered) +1) 
        RowN = Right(Convert(varchar(10), Dateadd(mm, Row_Number() over(order by (Select NULL))-1 , d.FirstDiscovered),6),6)        
        from master..spt_values s1, master..spt_values s2 )  c
    pivot (Count(RowN) for RowN in (' + @cols1 + ')) p '

Exec sp_executesql @query

输出如下：

+-----------------+----------------+-------------+----------+--------------+--------+--------+--------+--------+--------+
| FirstDiscovered | lastdiscovered | Application | computer | Organisation | Apr 17 | May 17 | Jun 17 | Jul 17 | Aug 17 |
+-----------------+----------------+-------------+----------+--------------+--------+--------+--------+--------+--------+
| 2017-04-13      | 2017-08-01     | App A       | Comp 2   | Org 1        |      1 |      1 |      1 |      1 |      1 |
| 2017-05-01      | 2017-08-01     | App B       | Comp 2   | Org 1        |      0 |      1 |      1 |      1 |      1 |
| 2017-05-03      | 2017-05-08     | App A       | Comp 1   | Org 1        |      0 |      1 |      0 |      0 |      0 |
| 2017-06-13      | 2017-08-02     | App A       | Comp 3   | Org 2        |      0 |      0 |      1 |      1 |      1 |
| 2017-07-14      | 2017-08-02     | App A       | Comp 4   | Org 2        |      0 |      0 |      0 |      1 |      1 |
| 2017-07-14      | 2017-08-02     | App B       | Comp 3   | Org 2        |      0 |      0 |      0 |      1 |      1 |
+-----------------+----------------+-------------+----------+--------------+--------+--------+--------+--------+--------+