Dynamic dates Pivot

Sample Data from StartKPA table:

I wrote the following 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 ' '20180429'
)           as Aantallen
PIVOT
(
    COUNT(EndKpa)
    FOR [BookInventoryDate] IN ([20180430], [20180501], [20180502])
) AS pvt
ORDER BY Aantallen DESC

With the following results (Sorry couldn't get in in a table here):

The problem is that I want dynamic dates. So every day a new date will be added, and the 20180502 isn't showed because there is no data. So I want the results in the above table, but everyday a new day will be added (and no zero results like above). I don't want to add a new date every day manually to the query.

There are some posts about it already, but I couldn't get it to work:

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 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 ' '20180429'
        ) x
        pivot 
        (
            count(EndKpa)
            for PivotDate in (' + @cols + ')
        ) p '

execute(@query)

I

Try this query. I was not able to check the query since I don't have data. If there is an error, please let me know.

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, '');

I'm getting unique dates assuming StartKPA is a table and BookInventoryDate is a field of that table. If'I'm wrong, please change it.

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 '' ''20180429''
                )           as Aantallen
                PIVOT
                (
                    COUNT(EndKpa)
                    FOR [BookInventoryDate] IN ('+ @colsValues +')
                ) AS pvt
                ORDER BY Aantallen DESC';

EXECUTE(@query);

StackExchange Database Administrators Q#205551, answer score: 2