Combination of dynamic pivot and static pivot in SQL Server

Dynamic pivot combined combined with static aggregates

I have a table that looks something like this:

Place    State   Category CategCount MCount Buys Cost
London   UK      Old      3          NULL   22   4.50
London   UK      Old      6          5      3    22.00
Brussels BE      Young    2          NULL   4    3.50
Brussels BE      M        NULL       5      12   1.20
Brussels BE      M        NULL       2      1    1.20

I basically need to:

• Group by a number of fields (Place, State, Category in the example)
• Count per such group
• Sum MCount, Cost (and others, not in example) per group, these columns are static
• Pivot over Category and sum CategCount for each such grouped category (here Old, Young). This is the dynamic part

Result should look like:

Count  Place    State   Category SumMCount SumOld SumYoung SumCost SumBuys 
2      London   UK      Old      5         9      0        26.50   25
1      Brussels BE      Young    0         0      2        3.50    4
2      Brussels BE      NULL     7         0      0        2.40    13

I know how to get a dynamic pivot query (as per https://stackoverflow.com/a/38505375/111575) and I know how to do the static part. But I don’t know how to combine the two. Anybody any ideas? Maybe I go about it all wrong?

What I’ve got so far:

The following gives me the proper dynamic pivot results for Old and Young, but not sure how to add the count and the the ‘regular’ sums/aggregates to it:

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

SELECT @cols = @cols + QUOTENAME(Category) + ',' FROM (select distinct Category from #temp where CategCount IS NOT NULL) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end
--select (@cols) as bm

set @query = 
'SELECT * from 
(
    select
        sum(CategCount) as totalCatCount,
        Category
    from #temp
    group by Category
) src
pivot 
(
    max(totalCatCount) for Category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

Returning:

And the following is the ‘regular’ query without the pivoting:

select count(*) as count, place, state, category,
    sum(ISNULL(CategCount, 0)) as SumCatCount,
    sum(ISNULL(MCount, 0)) as SumMCount,
    sum(ISNULL(buys, 0)) as SumBuys,
    sum(Cost) as SumCost
from #temp
group by place, state, category

Returning:

But it should look something like this:

Answer

I have used your static pivot part of the query as the source of dynamic pivot. Create two sets of dynamic pivot column list. One for pivoting and the another with Coalesce() to select pivoted columns (to convert null into 0). If there is no categcount for any category then that category has been replaced with null (case when). Two more aliases for Category and SumCatCount have been created since those were used in pivot condition.

Here goes your answer:

 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)
 GO

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

db<>fiddle here