Restricting pivot field values

You can choose to select specific values of the pivot field. For example, using the EmployeeSales table, we can choose to look at only those records in the north region:

TRANSFORM COUNT(Region) SELECT Employee FROM EmployeeSales GROUP BY Employee PIVOT Region IN ("North");

This produces the following table:

Employee

North

John

2

Richard

3

Susan

 

Note that the value list after the IN keyword must be in brackets. The general form of this statement is:

TRANSFORM COUNT(field_a) SELECT field_b FROM table GROUP BY field_b PIVOT field_a; IN ("value 1", "value 2", "value 3", …., "value n");

See also:

Crosstabulations

Other types of SQL statements