返回範圍內每個日期的列

假設我有表 A：BookingsPerPerson

Person_Id    ArrivalDate    DepartureDate
123456       2012-01-01     2012-01-04
213415       2012-01-02     2012-01-07

我需要通過視圖實現以下目標：

Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
123456       2012-01-01     2012-01-04       1         1         1         1
213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

該系統用於活動，因此每次酒店預訂可能需要 1 到 15 天之間的任何時間，但不會超過這個時間。任何想法將不勝感激。

您可以使用該PIVOT函式來執行此查詢。我的答案將包括靜態和動態版本，因為有時使用靜態版本更容易理解它。

靜態數據透視是當您硬編碼要轉換為列的所有值時。

-- first into into a #temp table the list of dates that you want to turn to columns
;with cte (datelist, maxdate) as
(
   select min(arrivaldate) datelist, max(departuredate) maxdate
   from BookingsPerPerson
   union all
   select dateadd(dd, 1, datelist), maxdate
   from cte
   where datelist < maxdate
) 
select c.datelist
into #tempDates
from cte c

select *
from
(
   select b.person_id, b.arrivaldate, b.departuredate,
       d.datelist,
       convert(CHAR(10), datelist, 120) PivotDate
   from #tempDates d
   left join BookingsPerPerson b
       on d.datelist between b.arrivaldate and b.departuredate
) x
pivot
(
   count(datelist)
   for PivotDate in ([2012-01-01], [2012-01-02], [2012-01-03],
             [2012-01-04], [2012-01-05], [2012-01-06] , [2012-01-07])
) p;

結果（參見SQL Fiddle With Demo）：

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1

動態版本將生成要轉換為列的值列表：

DECLARE @cols AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

;with cte (datelist, maxdate) as
(
   select min(arrivaldate) datelist, max(departuredate) maxdate
   from BookingsPerPerson
   union all
   select dateadd(dd, 1, datelist), maxdate
   from cte
   where datelist < maxdate
) 
select c.datelist
into #tempDates
from cte c


select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120)) 
                   from #tempDates
           FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')

set @query = 'SELECT person_id, arrivaldate, departuredate, ' + @cols + ' from 
            (
               select b.person_id, b.arrivaldate, b.departuredate,
                   d.datelist,
                   convert(CHAR(10), datelist, 120) PivotDate
               from #tempDates d
               left join BookingsPerPerson b
                   on d.datelist between b.arrivaldate and b.departuredate
           ) x
           pivot 
           (
               count(datelist)
               for PivotDate in (' + @cols + ')
           ) p '

execute(@query)

結果是一樣的（參見SQL Fiddle With Demo）：

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1

引用自：https://dba.stackexchange.com/questions/25809