How to create following Pivot table?

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.

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..