January 9, 2014 at 12:07 am
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
January 9, 2014 at 7:45 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply