Combination of dynamic pivot and static pivot in SQL Server

Question

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 1

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

Answer 2

Here I am sharing another answer which is same but as suggested by @Anthony Hancock dynamic column names for pivot have been created with string_agg() instead of stuff() and xml path for(). It's way too faster and more readable (for SQL Server 2017 and onward)

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


select @cols =string_agg(category,',') from (
select distinct category FROM #temp  where CategCount is not null )t


select @colsForSelect= STRING_AGG(category,',') from 
(select distinct 'coalesce('+category+',0) '+category category FROM #temp  where CategCount is not null )t


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)

Answer 3

Thanks to @Larnu in the comments for pointing me in the right direction. His/her statement on "you cannot JOIN a static to a dynamic query" and that either all or nothing has to be dynamic, prompted me to build onto the dynamic part and simply extend it.

I thought I needed to repeat the columns somehow in the PIVOT section, but that appears to not be the case. Only the column you want to pivot, apparently (logically so, once you think about it).

The only part I haven't figured out yet is how to get rid of NULL in the resulting set, hopefully someone answers with that in mind ;).

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
        count(*) as count,
        Place,
        State,
        Category,
        Category as CatPivot,
        sum(ISNULL(CategCount, 0)) as TotalCatCount,
        sum(ISNULL(Buys, 0)) as SumBuys,
        sum(ISNULL(Cost, 0)) as SumCost,
        sum(ISNULL(MCount, 0)) as SumMCount
    from #temp
    group by Category, Place, State
) src
pivot 
(
    max(TotalCatCount) for CatPivot in (' + @cols + ')
) piv'

execute(@query)