Yowch! Pivot breaks when used as a double pivot in that manner. Either that or I'm seriously missing something that has gone wrong with your code.
If we run the million row data generator from the article...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "Company" has a range of "AAA" to "BBB" non-unique 3 character strings
-- Column "Amount has a range of 0.0000 to 9999.9900 non-unique numbers
-- Column "Quantity" has a range of 1 to 50,000 non-unique numbers
-- Column "Date" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Columns Year and Quarter are the similarly named components of Date
-- Jeff Moden
SELECT TOP 1000000 --<<Look! Change this number for testing different size tables
RowNum = IDENTITY(INT,1,1),
Company = CHAR(ABS(CHECKSUM(NEWID()))%2+65)
Amount = CAST(ABS(CHECKSUM(NEWID()))%1000000/100.0 AS MONEY),
Quantity = ABS(CHECKSUM(NEWID()))%50000+1,
Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
Year = CAST(NULL AS SMALLINT),
Quarter = CAST(NULL AS TINYINT)
FROM Master.sys.SysColumns t1
--===== Fill in the Year and Quarter columns from the Date column
SET Year = DATEPART(yy,Date),
Quarter = DATEPART(qq,Date)
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE #SomeTable3
ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_#SomeTable3_Cover1
ON dbo.#SomeTable3 (Company, Year)
INCLUDE (Amount, Quantity, Quarter)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
It does, in fact, create a million rows...
SELECT COUNT(*) FROM #SomeTable3
(1 row affected)
If we take out the aggregation in the first SELECT and replace it with just a "*" and remove the GROUP BY, it should return a million rows...
[Qtr_Qty]=CONVERT( CHAR(1), Quarter)+'_Qty',
[Qtr_Amt]=CONVERT( CHAR(1), Quarter)+'_Amt',
) [pt] PIVOT(SUM([Quantity]) FOR [Qtr_Qty] IN([1_Qty],
AS [qty] PIVOT(SUM([Amount]) FOR [Qtr_Amt] IN([1_Amt],
--GROUP BY [Company],
BUT, IT DOESN"T RETURN THE MILLION ROWS! It's actually missing 10's of thousands of rows...
(968031 rows affected)
Although I'm not sure that Microsoft supports your method of doing a multi-column pivot, it looks like the PIVOT operator cause a "break" and doesn't consider all of the rows. Either that, or the optimizer doesn't handle the way the column names were dynamically created.
Since it's also more complex than a CROSS TAB, I'll never even consider using PIVOT especially since we just proved this method "breaks" and produces the wrong answers because it ignores 10's of thousand of rows. And, yes, I checked. The derived table in the FROM clause produces a million rows and even when saving that as a Temp Table and using that Temp Table in place of the derived table, you still end up with the missing rows and the wrong answers.
p.s. I also understand why you used MAX but since the PIVOT is also NOT doing an actual PIVOT on this, you need to use a SUM but it still doesn't come up with the correct answer because it's still missing rows.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)