SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

2 rows affected

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 '

select @query;

(No column name)
SELECT Aantallen, BookinventoryDate, [2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08] from ( SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA100)>80 THEN '>80' WHEN (EndKPA100) BETWEEN 70 AND 80 THEN '70-80' WHEN (EndKPA100) BETWEEN 65 AND 70 THEN '65-70' WHEN (EndKPA100) 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 ([2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08]) ) p

(No column name)

SELECT Aantallen, BookinventoryDate, [2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08] 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 ([2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08])
) p