Want pivot table class

[dbmanfwh]

Any,

I need class that can make pivot table by specifying field electively.

Want help if have someone.

[Antonio Linares]

Moon,

What is it a pivot table ?

Could you provide an example ? thanks

[dbmanfwh]

Dear Antonio,

I am working with DBF File.
I wish to can make pivottable rapidly like this.
http://courses.oreillyschool.com/dba1/dba110.html

Thank you.

[Antonio Linares]

Moon,

You can use FWH function ArrTranspose()

You can review an example of its use in FWH\source\classes\database.prg

[nageswaragunupudi]

Do you want for dbf or MySql?

[dbmanfwh]

Dear Rao,

for dbf.
When there are a lot of fields to dbf,
Should like to form automatically if specify two specification field.

[nageswaragunupudi]

There are several ways to construct pivot data.
I provide one sample

Code: Select all | Expand

/***   PivotDBF.PRG*   Author: G.N.Rao, India*   Mar 09-2015 07:11 PM**/#include "FiveWin.Ch"#include "ord.ch"#include "xbrowse.ch"//----------------------------------------------------------------------------//REQUEST DBFCDX//----------------------------------------------------------------------------//function Main()   local aPivot   CreateSampleDBF()   aPivot   := ReadPivotTable( "PVTDATA.DBF", "REGION", "PRODUCT", "SALES" )   BrowsePivot( AClone( aPivot ), "PRODUCT" )   // Invert Pivot   aPivot   := ArrTranspose( aPivot )   aPivot[ 1, 1 ] := "PRODUCT"   BrowsePivot( AClone( aPivot ), "REGION" )return (0)//----------------------------------------------------------------------------//init procedure PrgInit    SET DATE ITALIAN    SET CENTURY ON    SET TIME FORMAT TO "HH:MM:SS"    SET EPOCH TO YEAR(DATE())-50    SET DELETED ON    SET EXCLUSIVE OFF    RDDSETDEFAULT( "DBFCDX" )    XbrNumFormat( 'A', .t. )    SetKinetic( .f. )    SetGetColorFocus()    SetBalloon( .t. )return//----------------------------------------------------------------------------//static function BrowsePivot( aPivot, cColFld )   local oDlg, oFont, oBrw, aHead   aHead       := aPivot[ 1 ]   ADel( aPivot, 1, .t. )   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14   DEFINE DIALOG oDlg SIZE 760,250 PIXEL FONT oFont ;      TITLE "PIVOT TABLE"   @ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;      DATASOURCE aPivot AUTOCOLS HEADERS aHead ;      CELL LINES FOOTERS NOBORDER   WITH OBJECT oBrw      :SetGroupHeader( cColFld, 2, Len( aHead ) )      :SetGroupTotal( cColFld, "TOTAL" )      AEval( :aCols, { |o| o:nFooterType := AGGR_SUM }, 2 )      :nStretchCol   := 1      :MakeTotals()      :CreateFromCode()   END   ACTIVATE DIALOG oDlg CENTERED   RELEASE FONT oFontreturn nil//----------------------------------------------------------------------------//static function ReadPivotTable( cDbf, cRowFld, cColFld, cValFld )   local cPath, oCn, oRs, cSql, cCol   local aCols    := {}   local aPivot   := {}   local lUseCase   cDbf     := TrueName( cDbf )   cPath    := cFilePath( cDbf )   cDbf     := cFileNoExt( cDbf )   oCn      := FW_OpenAdoConnection( cPath, .t. )   lUseCase := ! FW_RDBMSName( oCn ) $ "DBASE,MSACCESS"   // Get Column Names   oRs      := FW_OpenRecordSet( oCn, "SELECT DISTINCT " + cColFld + " FROM " + cDbf )   aCols    := oRs:GetRows()   oRs:Close()   oRs      := nil   aCols    := ArrTranspose( aCols )[ 1 ]   // Prepare Pivot Table SQL   cSql  := "SELECT " + cRowFld    for each cCol in aCols      if lUseCase         cSql  += ", SUM( CASE WHEN " + cColFld + " = '" + cCol + "' THEN " + cValFld + " ELSE 0 END ) AS " + cCol      else         cSql  += ", SUM( IIF( " + cColFld + " = '" + cCol + "', " + cValFld + ", 0 ) ) AS " + cCol      endif   next   cSql += " FROM " + cDbf + " GROUP BY " + cRowFld   // ready   oRs      := FW_OpenRecordSet( oCn, cSql )   aPivot   := oRs:GetRows()   oRs:MoveFirst()   oRs:Close()   oCn:Close()   AIns( aCols, 1, cRowFld, .t. )  // First column header of first column   AIns( aPivot, 1, aCols,  .t. )  // Make 1st row the Header Rowreturn aPivot//----------------------------------------------------------------------------//static function CreateSampleDBF()   local aCols    := { ;      { "REGION",    'C',  10,   0  }, ;      { "PRODUCT",   'C',  10,   0  }, ;      { "SALES",     'N',  14,   2  }  }   local aRegions    := { "NORTH", "EAST", "WEST", "SOUTH" }   local aProducts   := { "DESKTOP", "LAPTOP", "TABLET", "MOBILE" }   local n, aData := {}   DBCREATE( "PVTDATA.DBF", aCols, "DBFCDX", .t., "DAT" )   for n := 1 to 10000      DAT->( DBAPPEND() )      DAT->( FieldPut( 1, aRegions[  HB_RandomInt( 1, 4 ) ] ) )      DAT->( FieldPut( 2, aProducts[ HB_RandomInt( 1, 4 ) ] ) )      DAT->( FieldPut( 3, HB_Random( 1000, 9999 ) ) )   next n   CLOSE DATreturn nil//----------------------------------------------------------------------------// 

[dbmanfwh]

Dear Rao,

Thank you.
Your solution is always wonderful.

[nageswaragunupudi]

We added pivot table support in 15.03.

Now it is as simple as:

Code: Select all | Expand

   local cDbf := "PVTDATA.DBF"   local oCn   oCn   := FW_OpenAdoConnection( cFilePath( TrueName( cDbf ) ) )   cDbf  := cFileNoExt( cDbf )   XBROWSER FW_AdoPivotArray( oCn, cDbf, "REGION", "PRODUCT", "SALES", "SUM" ) 

oBrw:InvertPivot() inverts the pivot and displays