Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Is Pivot Worth it?

I’ve been trying to learn more about the T-SQL changes in SQL Server 2005/2008 over time. I don’t have requirements to write a lot of T-SQL, so I have to futz around with it and just try things I see in articles or presentations. One that I’ve been meaning to play with for some time is the PIVOT command.

This command is designed to (from BOL): “change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.”

That’s confusing, but what this basically does is build a cross tab report. I hate doing this stuff in the db, since I think it’s a waste of resources, but I’m learning, so let’s try something. The examples in BOL work, but I wanted to check them, so I started with Jeff Moden’s Cross Tabs and Pivots, Part 1. That was easier to read, and it’s a great introduction. So I build a table.

CREATE TABLE Repairs 
( AUTO VARCHAR(20)
, RepairDate DATE
, Repair VARCHAR(20)
, cost NUMERIC(6, 2)
)
GO
INSERT Repairs SELECT 'Prius', '7/1/2007', 'Oil Change', 75
INSERT Repairs SELECT 'Prius', '10/1/2007', 'Oil Change', 75
INSERT Repairs SELECT 'Prius', '1/1/2008', 'Oil Change', 75
INSERT Repairs SELECT 'Prius', '4/1/2008', 'Oil Change', 75
INSERT Repairs SELECT 'Prius', '7/1/2008', 'Oil Change', 75
INSERT Repairs SELECT 'Prius', '9/1/2009', 'Tires', 360
INSERT Repairs SELECT 'Truck', '8/1/2007', 'Oil Change', 95
INSERT Repairs SELECT 'Prius', '11/1/2007', 'Oil Change', 95
INSERT Repairs SELECT 'Prius', '2/1/2008', 'Oil Change', 95
INSERT Repairs SELECT 'Prius', '5/1/2008', 'Oil Change', 95
INSERT Repairs SELECT 'Prius', '8/1/2008', 'Oil Change', 95
INSERT Repairs SELECT 'Truck', '2/1/2009', 'Tires', 560
INSERT Repairs SELECT 'Porsche', '5/1/2008', 'Oil Change', 120
INSERT Repairs SELECT 'Porsche', '4/1/2009', 'Oil Change', 120



I have repairs and some dates for my cars. What I was trying to do is take the table and pivot it so I can see the cost per repair per year per car. In other words, take this:



pivot1



and turn it into this:



pivot2





I looked over the examples I had, and then wrote this:



SELECT 
Datepart( yyyy, RepairDate) 'Year',
AUTO,
Sum(Coalesce([Oil Change],0)) 'Oil Changes',
Sum(Coalesce([Tires],0)) 'Tires'
FROM ( SELECT AUTO, RepairDate, Repair, Cost
FROM repairs
) AS Expenses
PIVOT( SUM( cost) FOR Repair IN ([Oil Change], [Tires])) AS pvt
Group BY
Datepart( yyyy, RepairDate)
, AUTO
ORDER BY Datepart( yyyy, RepairDate) , Auto



Which works. I choose the columns I wanted (in this case ‘Oil Changes’ and ‘Tires’) to pivot on and then wrote the statement. It works, but it seems hard to read, to me. I tend to agree with Jeff’s assertion in his article that a simple CASE statement is easier to read.



SELECT 
Datepart( yyyy, RepairDate) 'Year',
AUTO,
SUM(CASE WHEN REPAIR = 'Oil Change' then Cost else 0 end) 'Oil Changes',
SUM(CASE WHEN REPAIR = 'Tires' then Cost else 0 end) 'Tires'
FROM repairs
Group BY
Datepart( yyyy, RepairDate)
, AUTO
ORDER BY Datepart( yyyy, RepairDate) , Auto



But that’s me. I haven’t messed with dynamic columns or other changes, but for now I’m not sold that a PIVOT really is that helpful.

Comments

Posted by Marianne Collins on 11 May 2010

I agree, although there are developers on my team who use it extensively.  I've taken several of their examples and compared performance to the equivalent case statement - and the case statement usually has equal or better performance.

Posted by VaidyaVS on 11 May 2010

Urgent. Need a small help. To create SP with the following conditions.

Input

CompanyCode

Output

CompanyCode

PayeeCode

AdviserCode

AccountNumber

CustomerNumber

Period

TrailCommission

UpfrontCommission

InsuranceCommission

OngoingASF

OneOffASF

Adjustment

PlanServiceFee

Processing

From tFactAdviserRevenue and the supporting dimension tables, get the 1 month, 3 month, 6 month, 9 month, 12 month revenue (from the last month end) and last financial year figures (from the last month end)

The data comes from the following tables:

tFactAdviserRevenue

vsDimPayeeAdviser

vsDimAccount

Use a CASE statement for each revenue type column.

Use a cursor to do each period.

CompanyCode – Company code from vsDimAccount

PayeeCode – PayeeCode from vsDimPayeeAdviser

AdviserCode – AdviserCode from vsDimPayeeAdviser

AccountNumber – AccNumber from vsDimAccount

CustomerNumber – CustNo from vsDimAccount (primary owner)

Period – 1m, 3m, 6m, 9m, 12m or FY

TrailCommission – Sum of Amount where RevenueType = S

UpfrontCommission - Sum of Amount where RevenueType = C

InsuranceCommission - Sum of Amount where RevenueType = I

OngoingASF - Sum of Amount where RevenueType = A

OneOffASF - Sum of Amount where RevenueType = F

Adjustment - Sum of Amount where RevenueType = Z

PlanServiceFee - Sum of Amount where RevenueType = B

Exclude dealer code 0999 (non-advised).

Comments

Grant EXECUTE to dfs_firstnet role

Posted by jcrawf02 on 12 May 2010

Vaiydeyanathan, you should post that in the forums, not on Steve's blog. To get you started, see www.sqlservercentral.com/.../61537 and post your DDL and sample data in that format, along with the expected results, and an explanation of what you've tried so far, and what's not working.

Hope that helps,

Jon

Posted by jcrawf02 on 12 May 2010

Nice article Steve, and I agree, Pivot isn't easier to read or use, and given that you have to know the columns you're pivoting over (unless you're using dynamic SQL to generate) it's a somewhat stunted tool.

Do you actually have to change the oil every three months? Or do you do that out of habit? My Civic goes a lot longer than that between maintenance.

Posted by Steve Jones on 12 May 2010

LOL, no, in the Prius I change the oil about every 5k miles, so it's around 4 months for me. The engine doesn't run as much and I use synthetic. I go about 6k in the Porsche w/ synthetic, so that's about once a year.

I hadn't thought about dynamic SQL, and that might make Pivot easier to deal with, but I still think for simple conversions, the CASE makes more sense to me.

Posted by Seth Phelabaum on 12 May 2010

Pivot feels fancy... but I find it much harder to read and I personally still use cross tabs even in 2008.

Leave a Comment

Please register or log in to leave a comment.