How to create following Pivot table ?

Question

Hi!

Can we create pivot table with Multiple columns and each column contains multiple rows.

For example...........

Database Table:

        BatchID        BatchName      Chemical      Value
   --------------------------------------------------------
        BI-1           BN-1           CH-1             1

        BI-2           BN-2           CH-2             2
   --------------------------------------------------------

This is the table , i need to display like below in Excel Sheet

                        BI-1             BI-2
                        BN-1             BN-2
     ------------------------------------------
     CH-1               1                null
     ------------------------------------------
     CH-2               null             2
     ------------------------------------------

Here BI-1,BN-1 are two rows in a single columns i need to display chemical value as row of that.

Could Please help me to solve this problem.

Thank You.

Answer 1

Its bit difficult to get it in the format you want however a closer option would be as below -

Sum of  Value    BatchName  BatchID
        BN-1    BN-1 Total  BN-2    BN-2 Total  Grand Total
Chemical    BI-1            BI-2        
CH-1    1   1       NULL    NULL        1
CH-2        NULL    NULL        1   1       1
Grand Total 1   1       1   1       2

Process -

1. Create a pivot table
2. Add checmical to row field
3. Add Batch Name and Batch ID fields to column field
4. Add value to data items field.

Hope this helps... Cheers..