SQL Server 2019

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

create table #temp
(
    Place nvarchar(20),
    State nvarchar(20),
    Category nvarchar(20) null,
    CategCount int null,
    MCount int null,
    Buys int,
    Cost int
)
    
insert into #temp values ('London', 'UK', 'Old', 3, NULL, 22, 4.50)
insert into #temp values ('London', 'UK', 'Old', 6, 5, 3, 22.00)
insert into #temp values ('Brussels', 'BE', 'Young', 2, NULL, 4, 3.50)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 5, 12, 1.20)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 2, 1, 1.20)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
DECLARE @colsForSelect  AS NVARCHAR(MAX)='';

SET @cols = STUFF((SELECT distinct ',' + quotename(category)
            FROM #temp  where CategCount is not null 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @colsForSelect = STUFF((SELECT distinct ',' + ' Coalesce('+quotename(category)+',0) '+ quotename(category)
            FROM #temp  where CategCount is not null 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--select (@cols) as bm

set @query = 
'SELECT count,place,state,(case when OldSumCatCount >0 then OldCategory else null end)Category,SumMCount, ' + @colsForSelect + ',SumCost,SumBuys from 
(
    select count(*) as count, place, state,category OldCategory, category,    
    sum(ISNULL(MCount, 0)) as SumMCount,
	sum(ISNULL(CategCount, 0)) as OldSumCatCount,
	sum(ISNULL(CategCount, 0)) as SumCatCount,
    sum(Cost) as SumCost,
	sum(ISNULL(buys, 0)) as SumBuys
    
from #temp
group by place , state, category
) src
pivot 
(
    max(SumCatCount) for Category in (' + @cols + ')
) piv
order by place desc,count'

execute(@query)

count	place	state	Category	SumMCount	Old	Young	SumCost	SumBuys
2	London	UK	Old	5	9	0	26	25
1	Brussels	BE	Young	0	0	2	3	4
2	Brussels	BE	null	7	0	0	2	13