There is another, and rather important, use of pivot
tables that I must now mention. In previous chapters I have underlined
the importance of binding variables , in other words of passing parameters to SQL queries.
Variable binding allows the DBMS kernel to skip the parsing phase (in
other words, the compilation of the statement) after it has done it
once. Keep in mind that parsing includes steps as potentially costly as
the search for the best execution path. Even when SQL statements are
dynamically constructed, it is quite possible, as you have seen in Chapter 8, to pass variables to them.
There is, however, one difficult case: when the end user can make
multiple choices out of a combo box and pass a variable number of
parameters for use in an
in list. The
selection of multiple values raises several issues:
Dynamically binding a variable number of parameters may not be possible with all languages (often you must bind all variables at once, not one by one) and will, in any case, be rather difficult to code.
If the number of parameters is different for almost every call, two statements that only differ by the number of bind variables will be considered to be different statements by the DBMS, and we shall lose the benefit of variable binding.
The ability provided by pivot tables to split a string allows us to pass a list of values as a single string to the statement, irrespective of the actual number of values. This is what I am going to demonstrate ...