question

TALENZ avatar image
TALENZ asked

Pivot Function in SQL Server

I am not sure that this is possible, but here is what I'm trying to do. For any given group of customers, I have current balance, number of customer accounts, amounts 30 days past due, amounts 60 days past due, and amounts 90 days past due. I can write the following: SELECT * FROM ( SELECT DATE ,ACCTNO ,CUSTOMER ,BALANCE ,30D_BALANCE ,60_D_BALANCE ,90_D_BALANCE FROM PRODUCTION ) a PIVOT( SUM(BALANCE) FOR DATE IN (TODAY)) AS Piv1 Can I add additional pivots?
t-sqlsql serverpivot
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
iainrobertson avatar image
iainrobertson answered
You can, by joining, but it might get a bit messy and also quite inefficient: select * from pvt1 join ( ) pvt2 on pvt1.Key = pvt2.Key etc... It might be better if you used a case statement, which I seem to recall is a bit more efficient anyway. (In fact, here's the Jeff Moden article that proves it http://www.sqlservercentral.com/articles/T-SQL/63681/)
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TALENZ avatar image TALENZ commented ·
So perhaps this isn't the optimal route to go down. Are case statements the best way to proceed?
0 Likes 0 ·
iainrobertson avatar image iainrobertson commented ·
I'd say so, both for performance and readability.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.