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:

You would have to put the static query into your dynamic query. For stuff like this, often it can be easier to first write the full static query, for a single requirement, and then make it dynamic. Then you know the query you are aiming for, and just need to make it so that it works dynamically. — Thom A, Mar 09 '21 at 14:00
@Larnu, I do have the static query, it's on the bottom of my post. But the thing I'm stuck with is combining the two. I mean, how to pivot over some columns and not others, but return all? Or do you suggest to write a dynamic query completely and ditch the pivot (i.e., do it "by hand")? — Abel, Mar 09 '21 at 14:05
@KaziMohammadAliNur, it became `0`. When I use `SUM` I want `NULL` to be treated as zero, as opposed to making the whole sum NULL, which is why the `ISNULL(X, 0)` is all over the place. When category is M it means get `MCount`, i.e. a fixed column, no pivot, when it is some non-fixed category, like `Young`, it should pick the `CatCount` column and pivot — Abel, Mar 09 '21 at 14:07
Like I said, @Abel **"*You would have to put the static query into your dynamic query.**"* YOu can't `JOIN` a static query to a dynamic query; the whole thing needs to be static or dynamic. — Thom A, Mar 09 '21 at 14:24
You should be aware that not only do Views not allow dynamic code, they require a fixed (pre-defined) column set. Dynamic SQL can use a View, but a View cannot use nor be dynamic. — RBarryYoung, Mar 09 '21 at 15:16
@RBarryYoung, thanks, I wasn't aware of that. So even if you'd stuff this in a stored procedure, the view still won't allow it, right? Though I could do the inverse, write an SP instead of a View, which doesn't have that limitation. — Abel, Mar 09 '21 at 17:03
@Abel Correct. There’s an obscure convoluted trick or two to get around the dynamic SQL limitation, but even then Views must have a pre-defined column set, just like tables and table-valued functions. Procedures have neither restriction. — RBarryYoung, Mar 09 '21 at 17:31

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-05-29T18:57:24.493

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

Cool! We posted at the same time, I figured parts of this out while you were posting this. But I like your solution better as it doesn't have `NULL` in the result set! Thanks! — Abel, Mar 09 '21 at 14:38
Also, you use the `XML PATH` hack, I've seen it before but thought it wasn't needed. Will use this as base for the real-world query of the same :). — Abel, Mar 09 '21 at 14:40
That's really cool to have same timing. Cheer! You are most welcome. Best wishes. — Kazi Mohammad Ali Nur Romel, Mar 09 '21 at 14:42
I noticed the MS SQL server won't let you do `max(isnull(sumcatcount, 0)) for Category in ...` in the pivot statement and I'm surprised at how comparatively complex the solution is to get rid of `NULL` in the result set. But hey, it works! :) — Abel, Mar 09 '21 at 14:48
If you have SQL Server 2017 or later, you should be able to use string_agg instead of the FOR XML trick. — Anthony Hancock, Mar 09 '21 at 14:55
That's true! I just forgot every now and then. Actually I just like to write this syntax. Thanks. — Kazi Mohammad Ali Nur Romel, Mar 09 '21 at 15:01

score 2 · Answer 2 · answered Mar 09 '21 at 14:37

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)

score 2 · Answer 3 · answered Mar 09 '21 at 15:13

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)

Yeah, the `STRING_AGG` function was a great (albeit late) addition to SQL Server. We deploy to Azure, but my local db is still a bit old. Good reason to upgrade! Tx again! — Abel, Mar 09 '21 at 17:01

Combination of dynamic pivot and static pivot in SQL Server

Dynamic pivot combined combined with static aggregates

What I've got so far:

But it should look something like this:

3 Answers3