There are a few ways to do this.

Here are 3:

SELECT
e.EmployeeID
,e.FirstName
,e.LastName
,ShipToMexico=CASE WHEN MAX(o.ShipCountry) IS NULL THEN 'N' ELSE 'Y'...

Are you only getting this message during reindexing, perhaps? 

It's a known-but-sparsely-documented issue with REORGANIZE that it can lead to this error even when you have plenty of space...

I use a very sophisticated system: my frequently used queries are in a text file, with leading comments including a name/title for the query, and I use Ctrl+F 🙂

Perhaps it is simpler to avoid PIVOT/UNPIVOT altogether.

Something like this approach might work for you:

SELECT
title =ISNULL(contacttype, CASE WHEN GROUPING(contacttype)=0 THEN...

Yeah, if we assume there is no possibility of parent companies with the same name then we can make it simpler; without knowing that for sure, though, I figured I'd...

In the future, posting the sample data as a create table script with inserts for the sample data would help us out quite a bit (otherwise to test solutions we...

Indeed. Changing the frame to ROWS UNBOUNDED PRECEDING is responsible for that difference (instead of just letting it use the default RANGE like I did).

If my window functions...

Also, just to be frank, with the query being as it is, even with statistics as tailored for this query as you can, it's going to be difficult/impossible for SQL...

If you can, post the actual execution plans of the query when it runs in 1 minute and when it runs in 10 minutes. 

That could provide some clues...

I'm aware of that; they are ignored in computing the sum, and the warning only appears if your ANSI_WARNINGS is ON, so the bit about the warning's not even always...

As expected.

From my initial post:

Of course, you have to have something that defines the order of each row; the OP will have to adapt to...

Yeah, just to clarify, NULLs are ignored by SUM anyway, so wrapping the column values being summed in an ISNULL is redundant (EDIT: as pointed out below, not completely true);...

No worries; I just wanted to make sure I didn't just wait around in the case it was directed to me 🙂

Assuming the desired order for the cumulative...

@j-2 Livingston SQL:

Directed to the OP, or to me? 🙂