Pivot... I don't think it's possible...
-
Heh... I wonder if the use of UNPIVOT constitutes cheating too much in this case?
Still, some awesome code... Thanks Jeff W and Peter. I really didn't think it could be done, but should know better especially with folks like the two of you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Riding JW's solution...
--Peso 3SELECTp.Company,p.[Year],COALESCE(p.Amt1, 0.0) As [Q1 Amt],CAST(COALESCE(p.Qty1, 0) AS INT) As [Q1 Qty],COALESCE(p.Amt2, 0.0) As [Q2 Amt],CAST(COALESCE(p.Qty2, 0) AS INT) As [Q2 Qty],COALESCE(p.Amt3, 0.0) As [Q3 Amt],CAST(COALESCE(p.Qty3, 0) AS INT) As [Q3 Qty],COALESCE(p.Amt4, 0.0) As [Q4 Amt],CAST(COALESCE(p.Qty4, 0) AS INT) As [Q4 Qty],COALESCE(p.Amt1, 0.0) + COALESCE(p.Amt2, 0.0) + COALESCE(p.Amt3, 0.0) + COALESCE(p.Amt4, 0.0) AS [Total Amt],CAST(COALESCE(p.Qty1, 0) + COALESCE(p.Qty2, 0) + COALESCE(p.Qty3, 0) + COALESCE(p.Qty4, 0) AS INT) As [Total Qty]FROM(SELECTCompany,[Year],QuarterName,SUM(Amount) AS [Value]FROM(SELECTCompany,[Year],'Amt' + STR([Quarter], 1) AS QuarterName,SUM(Amount) AS AmountFROMjbmTestGROUP BYCompany,[Year],[Quarter]UNION ALLSELECTCompany,[Year],'Qty' + STR([Quarter], 1),SUM(Quantity)FROMjbmTestGROUP BYCompany,[Year],[Quarter]) AS dGROUP BYCompany,[Year],QuarterName) AS tPIVOT(SUM(t.[Value])FOR t.QuarterName IN (Amt1, Amt2, Amt3, Amt4, Qty1, Qty2, Qty3, Qty4)) AS pORDER BYp.Company,p.[Year]SQL Profiler
CPU Dur Reads---- ----- -----Jeff 2531 4270 5304JW 2 9938 16935 10608Peso 2 4078 6846 5304Peso 3 2422 4451 10608N 56°04'39.16"
E 12°55'05.25" -
It's funny how changing the CTE into a derived table improved performance like that. I'll dig into that and see why.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The trick was not to convert CTE to derived table.
The performance gain was to subaggregate the table before making the pivot.
N 56°04'39.16"
E 12°55'05.25" -
Same conclusion I came to... Thanks Peter. It also means that it could be done using a CTE with the same performance if the data is pre-aggregated like you did in your good code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Your original solution will also gain from preaggregration like this
-- Jeff 2SELECTCompany,[Year],SUM(CASE WHEN [Quarter] = 1 THEN Amount ELSE 0.0 END) AS [Q1 Amt],SUM(CASE WHEN [Quarter] = 1 THEN Quantity ELSE 0 END) AS [Q1 Qty],SUM(CASE WHEN [Quarter] = 2 THEN Amount ELSE 0.0 END) AS [Q2 Amt],SUM(CASE WHEN [Quarter] = 2 THEN Quantity ELSE 0 END) AS [Q2 Qty],SUM(CASE WHEN [Quarter] = 3 THEN Amount ELSE 0.0 END) AS [Q3 Amt],SUM(CASE WHEN [Quarter] = 3 THEN Quantity ELSE 0 END) AS [Q3 Qty],SUM(CASE WHEN [Quarter] = 4 THEN Amount ELSE 0.0 END) AS [Q4 Amt],SUM(CASE WHEN [Quarter] = 4 THEN Quantity ELSE 0 END) AS [Q4 Qty],SUM(Amount) AS [Total Amt],SUM(Quantity) AS [Total Qty]FROM(SELECTCompany,[Year],[Quarter],SUM(Amount) AS Amount,SUM(Quantity) AS QuantityFROMjbmTestGROUP BYCompany,[Year],[Quarter]) AS dGROUP BYCompany,[Year]ORDER BYCompany,[Year]SQL Profiler
CPU Dur Reads---- ----- -----Jeff 2531 4270 5304Jeff 2 1297 2206 5304JW 2 9938 16935 10608Peso 2 4078 6846 5304Peso 3 2422 4451 10608N 56°04'39.16"
E 12°55'05.25" -
That really puts the stuff on the Pivot method, huh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
By the way, Peter... you machine is about twice as fast as more poor ol' 6 year old single processor 1.8 GHz box... what are you using?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
HP 8710p laptop.
2.4 GHz Intel Centrino Pro
2gb ram.
Single sata 3.5" harddrive.
Microsoft SQL Server 2005 Developer Edition - 9.00.3215.00
If I tell you what I use at home, I have to kill you later.
I have a better db server at home than at work.
N 56°04'39.16"
E 12°55'05.25" -
Excellent work - I did not even think about pre-aggregating the data but it makes sense. But again, as I suspected, the original cross-tab version still performs better than PIVOT.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Awesome posts and code, guys! Thanks so much for sharing. This answered the exact question I was having. Guess I'll go with the crosstab option.
-
Hey folks... especially Jeff Williams and Peter Larson... thanks for the great help. Like I said, I'm kinda new at 2k5 and you just don't find the kind of outstanding examples in BOL that you two gents cranked out. On top of that, I got a real surprise when Peter did his usual great bit of performance testing.
Heh... Jeff Williams has already started to refer people to this thread. It shows a couple of really good methods not only from the original cross tab code, but in the examples that Jeff Williams and Peter wrote using Pivot. Good stuff here. Thanks again!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply