For what it's worth, this version breaks the 5 CPU sec barrier consistently and sometimes 5 secs elapsed time too. Still in 3rd place though.

PRINT '========== DWAIN IMPROVED (SLIGHTLY)...

BTW. Adding OPTION (MAXDOP 1) to SomewhereSomehow and Mark's gets these results (good improvement on CPU at expense of some elapsed time).

========== SomewhereSomehow ===========================================

SQL Server Execution Times:

...

Not as fast as CELKO or IMPROVED CELKO but a strong third place is this option:

--===== Conditionally drop the test table to make reruns in SSMS easier.

...

I think the reason no one has replied to you in 2 days is because you seem to have posted many questions, but none of them clearly enough for us...

Actually, CTEs are more like a more readable version of a derived table. I prefer them to derived tables because of the readability they add to a complex query.

They...

SomewhereSomehow (8/3/2012)

---

When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check...

Mike McIver (8/6/2012)

now have to stick my neck out, and put up or shut up on the Quarter version. This "report" is getting silly (45k rows, ridiculous format, performance...

Mike McIver (8/6/2012)Looks like Revenue "% O/(U) Prev Mo" is off a little here and there for nov and dec ... oops. Not to rub it in ... but...

You are probably better off using a Tally table for this:

CREATE TABLE #TempBet

(High int, Low int)

INSERT INTO #TempBet (Low, High)

SELECT 1, 3 UNION ALL

SELECT 2, 3 UNION All

SELECT 5, 8

;WITH...

Mike McIver (8/3/2012)

---

... May we see a "full year" version of the cross apply? Then we'll start...

mishaluba (8/2/2012)

---

Thank you for the article! Sorry if this is a silly question, but could somebody explain to me what is that little "x" doing on this line:

...

cliffb (8/2/2012)

---

There is a way to get NULL values back in an unpivot. It's not pretty but it works.

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

DROP TABLE #Orders

-- DDL and sample data...

Mike McIver (8/2/2012)

---

Good to know ... I was unawares.

Here's the last example done with pivot/unpivot ... a bit more code, performance not as good ... but much more maintainable/flexible (IMO)...

Cadavre, ChrisM, WayneS and jfogel -

Your positive comments are much appreciated. I was really hoping that few people had actually seen this approach because it does seem to be...

gsimard (8/2/2012)

---

Thanks for the article, very interresting.

Basically, your "other Unpivot" technique is a brillant way to use the new (from 2008) Table Value Constructor

Using the same method, from your first...