動態日期樞軸

StartKPA 表中的範例數據：

我寫了以下查詢：

SELECT      *
FROM    
(
           SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) BETWEEN 70 AND 80 THEN '70-80'
                                             WHEN (EndKPA*100) BETWEEN 65 AND 70 THEN '65-70'
                                             WHEN (EndKPA*100) BETWEEN 62 AND 65 THEN '62-65'
                                             WHEN (EndKPA*100) BETWEEN 60 AND 62 THEN '60-62'
                                             ELSE '<60' END as Aantallen
FROM        StartKPA
WHERE       BookInventoryDate > '20180429'
)           as Aantallen
PIVOT
(
   COUNT(EndKpa)
   FOR [BookInventoryDate] IN ([20180430], [20180501], [20180502])
) AS pvt
ORDER BY Aantallen DESC

得到以下結果（抱歉無法進入此處的表格）：

問題是我想要動態日期。所以每天都會添加一個新的日期，20180502 沒有顯示，因為沒有數據。所以我想要上表中的結果，但是每天都會添加一個新的一天（並且沒有像上面這樣的零結果）。我不想每天手動將新日期添加到查詢中。

已經有一些關於它的文章，但我無法讓它工作：

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

;with cte (datelist, maxdate) as
(
select min(BookInventoryDate) datelist, max(BookinventoryDate) maxdate
from StartKPA
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 Aantallen, BookinventoryDate, ' + @cols + ' from 
        (
          SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) BETWEEN 70 AND 80 THEN '70-80'
                                             WHEN (EndKPA*100) BETWEEN 65 AND 70 THEN '65-70'
                                             WHEN (EndKPA*100) BETWEEN 62 AND 65 THEN '62-65'
                                             WHEN (EndKPA*100) BETWEEN 60 AND 62 THEN '60-62'
                                             ELSE '<60' END as Aantallen
           FROM        #tempDates d
           LEFT JOIN   StartKPA kpa on d.datelist=kpa.bookinventorydate
           WHERE       BookInventoryDate > '20180429'
       ) x
       pivot 
       (
           count(EndKpa)
           for PivotDate in (' + @cols + ')
       ) p '

execute(@query)

我收到了一些錯誤消息，試圖改變一些東西，但我對它的理解不足以讓它工作。

有人可以幫忙嗎？謝謝！

試試這個查詢。由於沒有數據，我無法檢查查詢。如果有錯誤，請告訴我。

SELECT DISTINCT BookInventoryDate FROM StartKPA

DECLARE @colsValues AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename(datesYouWantAsColumns)
        FROM yourTable
        FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

StartKPA假設是一個表並且BookInventoryDate是該表的一個欄位，我得到了唯一的日期。如果我錯了，請更改它。

DECLARE @colsValues AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename(BookInventoryDate)
        FROM StartKPA
        FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max);

SET @query =   'SELECT      *
               FROM    
               (
                           SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN ''>80'' WHEN (EndKPA*100) BETWEEN 70 AND 80 THEN ''70-80''
                                                               WHEN (EndKPA*100) BETWEEN 65 AND 70 THEN ''65-70''
                                                               WHEN (EndKPA*100) BETWEEN 62 AND 65 THEN ''62-65''
                                                               WHEN (EndKPA*100) BETWEEN 60 AND 62 THEN ''60-62''
                                                               ELSE ''<60'' END as Aantallen
               FROM        StartKPA
               WHERE       BookInventoryDate > ''20180429''
               )           as Aantallen
               PIVOT
               (
                   COUNT(EndKpa)
                   FOR [BookInventoryDate] IN ('+ @colsValues +')
               ) AS pvt
               ORDER BY Aantallen DESC';

EXECUTE(@query); 

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