|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Pivot TableI need some help: (btw I'm running on SQL 2000, which according to my knowledge does not have the PIVOT function) I need to pivot information as follows: OrderDate StockCode BackOrderQty 01/01/06 010-0300-10-L2 10 02/01/06 010-0300-11-L2 15 01/02/06 010-0300-10-L2 10 etc.... with different items ordered on different dates. What I need to do is pivot the info as follows: sum of BackOrderQty StockCode OrderDate 01/01/06 02/01/06 03/01/06 ... etc... I don't know whether all dates are ordered on, and to set up the sum(case... for every single date is obviously defeating the purpose. Any help will be appreciated, Thanking you in advance Implementing crosstab type functionality (pivoting is the process of
changing the X / Y indicators and recalculating) using SQL is programming intensive and inflexible, however, there are existing examples you can work from: http://www.johnmacintyre.ca/codespct.asp http://www.sqlteam.com/item.asp?ItemID=2955 Perhaps the best solution would be to implement the pivot table on the application side using something like Excel's pivot table. The pivot table used by Excel is an ActiveX control and COM objects which can be hosted by any COM enabled application (like VB or HTML). http://www.cpearson.com/excel/pivots.htm http://www.microsoft.com/businesssolutions/excel_pivot_tables_collins.mspx http://office.microsoft.com/en-us/assistance/HA010346321033.aspx http://office.microsoft.com/en-us/FX012071631033.aspx If you are wanting to pivot 100MBs or GBs of transactional data, then you will want to import the data into an OLAP database (like Analysis Services) and use that as the source of the pivot table. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmda/html/odc_olapfaq.asp Show quote "CyberFox" <Cyber***@discussions.microsoft.com> wrote in message news:EE81E098-34A7-4200-82A4-3B4D3BFF4E2E@microsoft.com... > OK, I've now read a lot about the pivot table funcationality within SQL, > but > I need some help: (btw I'm running on SQL 2000, which according to my > knowledge does not have the PIVOT function) > > I need to pivot information as follows: > OrderDate StockCode BackOrderQty > 01/01/06 010-0300-10-L2 10 > 02/01/06 010-0300-11-L2 15 > 01/02/06 010-0300-10-L2 10 > etc.... with different items ordered on different dates. > > What I need to do is pivot the info as follows: > sum of BackOrderQty > StockCode OrderDate > 01/01/06 02/01/06 03/01/06 ... etc... > > I don't know whether all dates are ordered on, and to set up the > sum(case... > for every single date is obviously defeating the purpose. > > Any help will be appreciated, > Thanking you in advance > > |
|||||||||||||||||||||||
Other interesting topics