Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to pivot using pivot clause of sqlserver 2005 for three values Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 12:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:33 PM
Points: 47, Visits: 143
Dear all

Is it possible to formulate the query using pivote clause of sqlserver 2005.

( Table name is) BridgeHawra

--------

ReportDate, , Pole, DiameterCost,Flag,OuterDiaCost

01 jan 2014, ,1 , 4.5 ,yes,6

01 jan 2014, ,2 , 4.5 ,no,6

01 jan 2014, ,3 , 5.5 ,no,6

02 jan 2014, ,4 , 5.5 ,no,5.9

02 jan 2014, ,5 , 5.5 ,no,6

1)Pole Col will have unique value.

o2)One pole will have one DiameterCost, but n number of DiameterCost can be there.

I have to show a report which should show , count of poles and cost(DiameterCost* pole count of the day of each DiameterCost) for each date and DiameterCost.

as following


DateOfReport , PoleCountofDiameterCost4.5, DiameterCost4.5, PoleCountOfDiameterCost5.5,DiameterCost5.5

01 jan 2014 , 2 ,(2*4.5) ,1 ,(1*5.5)

02 jan 2014 , 0 ,0 ,2 ,(2*5.5)


yours sincerely





Post #1529216
Posted Thursday, January 9, 2014 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
Yes you have to use a cross tab. If you know the number of elements you can use a static cross tab. If the number of elements is not known it will have to be the dynamic version. You can read about them by following the links in my signature. You might also take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1529343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse