It looks like a CrossTab query to get data in a pivoted format.

The query itself doesn't look that bad except for the following issues:

The subquery aliased as y selects more columns than used in the outer query. I recommend to remove all columns not being part of the final query.

The WHERE clause for table aliased x is weird: There are three checks for an identical constant (value <> ': : : : '). Should be reduced to one check (I'd expect the query optimizer being smart enough to eliminate the dupe internally anyway...)

One reason the query suddenly fails could be a removed/disabled index leading to table scans and a heavy usage of tempdb...

It would help a lot if you could attach the actual execution plan as a sqlplan file together with the row count for each table, so we'd have some figures to look at.

If proper indexing can't reduce the time to query the data, preaggregation might be the way to go... But I don't think that's needed.