| ADOdb Library for PHP Manual | ||
|---|---|---|
| Prev | Pivot Tables | Next |
Since ADOdb 2.30, we support the generation of SQL to create pivot tables, also known as cross-tabulations. For further explanation read this DevShed Cross-Tabulation tutorial. We assume that your database supports the SQL case-when expression.
In this example, we will use the Northwind database from Microsoft. In the database, we have a products table, and we want to analyze this table by suppliers versus product categories. We will place the suppliers on each row, and pivot on categories. So from the table on the left, we generate the pivot-table on the right:
|
--> |
|
#
Query the main "product" table
#
Set the rows to SupplierName
#
and the columns to the values of Categories
#
and define the joins to link to lookup tables
#
"categories" and "suppliers"
#
include "adodb/pivottable.inc.php";
$sql = PivotTableSQL(
$gDB, #
adodb connection
'products
p ,categories c ,suppliers s', #
tables
'SupplierName', #
rows (multiple fields allowed)
'CategoryName', #
column to pivot on
'p.CategoryID
= c.CategoryID and s.SupplierID= p.SupplierID' #
joins/where
);
This will generate the following SQL:
SELECT SupplierName,
SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages",
SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments",
SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections",
SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products",
SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals",
SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry",
SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce",
SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood",
SUM(1) as Total
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
GROUP BY SupplierName
You can also pivot on numerical columns and generate totals by using ranges. This code was revised in ADODB 2.41 and is not backward compatible. The second example shows this:
$sql
= PivotTableSQL(
$gDB, # adodb connection
'products p ,categories c ,suppliers s', # tables
'SupplierName', # rows (multiple fields allowed)
array( # column ranges
' 0 ' => 'UnitsInStock <= 0',
"1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
"6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
"11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15',
"16+" => '15 < UnitsInStock'
),
' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
'UnitsInStock', # sum this field
'Sum ' # sum label prefix
);
Which generates:
SELECT SupplierName,
SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ",
SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5",
SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10",
SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15",
SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
SUM(UnitsInStock) AS "Sum UnitsInStock",
SUM(1) as Total,
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
GROUP BY SupplierName
| Prev | Home | Next |
| Caching of Recordsets | Up | Class Reference |