Skip to content
Advertisement

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:

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:

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:

Returning:

enter image description here

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

Returning:

enter image description here

But it should look something like this:

enter image description here

Advertisement

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:

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement