Pivot... I don't think it's possible...

Question

Pivot... I don't think it's possible...

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Riding JW's solution...

--Peso 3

SELECTp.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 Amount

FROMjbmTest

GROUP BYCompany,

[Year],

[Quarter]

UNION ALL

SELECTCompany,

[Year],

'Qty' + STR([Quarter], 1),

SUM(Quantity)

FROMjbmTest

GROUP BYCompany,

[Year],

[Quarter]

) AS d

GROUP BYCompany,

[Year],

QuarterName

) AS t

PIVOT(

SUM(t.[Value])

FOR t.QuarterName IN (Amt1, Amt2, Amt3, Amt4, Qty1, Qty2, Qty3, Qty4)

) AS p

ORDER BYp.Company,

p.[Year]

SQL Profiler

CPU Dur Reads

---- ----- -----

Jeff 2531 4270 5304

JW 2 9938 16935 10608

Peso 2 4078 6846 5304

Peso 3 2422 4451 10608

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

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"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Your original solution will also gain from preaggregration like this

-- Jeff 2

SELECTCompany,

[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 Quantity

FROMjbmTest

GROUP BYCompany,

[Year],

[Quarter]

) AS d

GROUP BYCompany,

[Year]

ORDER BYCompany,

[Year]

SQL Profiler

CPU Dur Reads

---- ----- -----

Jeff 2531 4270 5304

Jeff 2 1297 2206 5304

JW 2 9938 16935 10608

Peso 2 4078 6846 5304

Peso 3 2422 4451 10608

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

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"

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 10

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

mvee Hall of Fame Points: 3634 More actions · Answer 11

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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)