Yes it may try your patience Mister Magoo!

Reducing the Strain on Multiple Unpivots - who says you shouldn't read from a physical table?

This variation is very similar to the above. The difference is that this...

Multiple Unpivot Variation Using 16 Column Block and 5 Unpivots

I think this is just about my preferred refinement of the multiple unpivot base table approach just now.

It seems to edge...

Yeh, but it's quite symmetrical, albeit a little taller than the CTE version of the table join!

Trillion Row Base Table - Pushing Multiple Unpivots to the Limit

So what do you think folks? Will this one go fast?

Sorry Jeff, no cross joins!

WITH cte1 AS

(

SELECT...

Multiple Unpivots to the 100 Billion Level

This one might go fast too

In this variation 6 unpivots are used to create a base table with 1 million rows.

Just one...

Cascading Unpivots to the 100 Billion Level

This seems to be a viable approach too. I adjusted my cascading unpivots query to potentially release over a trillion rows (not...

THE TRILLION

Well the TRILLION did run to completion OK. A little over 69 hours which ain't bad for a modest spec VM with only 2GB RAM. ...

Cascading Unpivots

Or how about this variation to ratchet things up to the 9 billion level

WITH cte1 AS

(

SELECT A,B,C,O,P,Q FROM

(

SELECT 1 AS A1,1 AS A2,1 AS A3,1 AS A4,1...

Really very similar at the 1 billion level

Multiple Unpivots

It occurred to me that an interesting variation of the Gianluca Sartori UNPIVOT method would be to create a base table using multiple unpivots. This particular version...

It would have been helpful if you had posted some data

but does this sort of approach help you out?

IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL

DROP TABLE #FOO

SELECT 'Apple,Banana,Orange,Pineapple,Lemon,pear' AS BulkColumn...

I know the feeling

Yes Mister Magoo that really is a very elegant solution indeed.

I've had a look at comparing against my query and it sure is a close one to call.

Re the TRILLION...

Tom

I think you'll find my query is written in a different way which speeds it up by a factor of 4.