Very well illustrated. If you like PIVOT, stick with it, but this is certainly an alternative, especially if the other columns have slightly different conditional aggregations. Well done sir!
Glad I found your channel. Precisely what I was looking for. Subscribed immediately and activated the 🔔. Excellent subject selections. Keep them coming and THANK YOU for all efforts and time!
Not a fan of the Pivot syntax personally, but then I couldn't offer a better alternative! Pivoting by using case and aggregating is the way you would always accomplish this before they introduced the Pivot syntax, which as you point out, is just using the traditional case implementation anyway. I have used Pivot a few times to accomplish a more compact syntax which actually helps make other parts of the query easier to follow, however following what's going on with a Pivot someone else has written or even yourself in the past I find more challenging than if it were cased, particularly if it's used in dynamic sql, often the case for reporting where the columns aren't known in advance. Plus of course using case gives you far more control that you don't have with Pivot and is easier to debug and modify, in my experience this is also needed more often than not - at least with the user requirements I tend to encounter!
Very well illustrated. If you like PIVOT, stick with it, but this is certainly an alternative, especially if the other columns have slightly different conditional aggregations. Well done sir!
Thanks so much 👍.
Glad I found your channel. Precisely what I was looking for. Subscribed immediately and activated the 🔔. Excellent subject selections. Keep them coming and THANK YOU for all efforts and time!
This is a great way to find sums and averages, etc. Thanks for the pro-tip!
Thanks, I was very helpful.
This actually fits my solution better than pivot table.
That’s excellent.
Not a fan of the Pivot syntax personally, but then I couldn't offer a better alternative! Pivoting by using case and aggregating is the way you would always accomplish this before they introduced the Pivot syntax, which as you point out, is just using the traditional case implementation anyway. I have used Pivot a few times to accomplish a more compact syntax which actually helps make other parts of the query easier to follow, however following what's going on with a Pivot someone else has written or even yourself in the past I find more challenging than if it were cased, particularly if it's used in dynamic sql, often the case for reporting where the columns aren't known in advance. Plus of course using case gives you far more control that you don't have with Pivot and is easier to debug and modify, in my experience this is also needed more often than not - at least with the user requirements I tend to encounter!
Excellent points, I didn’t want everybody to think they should avoid using Pivot, it is still useful.
Can we have videos on how to calculate Month over Month, week over week and year over year metrics?
Great idea, I will put it on the list.
Ingenious! What more needs to be said?
why do we have use sum here , without using the sum logic seems correct to me
I understand where you are coming from but because we are grouping data we need to use an aggregate function otherwise the query would fail.
I may NEVER use the much maligned Pivot command again!
Comes down to personal preference.