Featured Database Articles

We have assembled the basic SELECT query, and run it to obtain the resulting dataset. Our objective in doing this is to be able to see the differences in the SELECT query and the crosstab query, which is essentially a "transformed" SELECT query that calculates and restructures the SELECT query dataset into a more useful presentation layout.

Notice that our SELECT query dataset only groups the totals vertically by customer company name and product category. This results in multiple records in many cases, making comparisons between different customers' total products more difficult. It is even more difficult to readily tell at a glance the composition of each customer's activity, totaled by product category, or even to easily discern a total quantity of products. The database with which we are practicing is quite small - if we can extrapolate this same scenario to a much larger database, it is easy to see why the simple SELECT query dataset might not meet our needs in a user-friendly way.

A crosstab query displays the same information, but groups it both horizontally and vertically so the datasheet is more compact and easier to analyze. We shall see the effect in our next steps, as we use the TRANSFORM statement to present our data in a crosstab, grouping by product category horizontally and by customer vertically.

15. Ensure that the query is saved as ACC06-Step1.

16. Shift back to SQL view, if necessary.

17. Skip a couple of lines below the existing query, and type a dotted line approximately the width of the query.

18. Skip a couple lines below the dotted line, and input the following crosstab query exactly as shown:

My intent in adding the TRANSFORM statement below the original SELECT query, after the "dividing line," is simply to offer a platform whereby we might see the two juxtaposed briefly, to allow for a comparison. Obviously we would not attempt to run the query in its present state.

We can see the rearrangement of the upper SELECT query into the lower TRANSFORM statement's layout: First, the TRANSFORM statement applies the COUNT aggregate function. We see that the TRANSFORM statement appears before the SELECT statement that it is "transforming."

After the mandatory SELECT statement, which, as we can easily note in our arrangement from above, is identical to the initial SELECT query (including the GROUP BY clause) , we can see the PIVOT clause, where we have placed the Categories.CategoryName field that once existed in the first line of the SELECT query. Recall that the PIVOT clause determines the column names for the crosstab - in our case, it sets up a column for every existing Category Name in the dataset under consideration.

19. Delete the top half of the existing text in the query - down to and including, the dotted line. Align the new crosstab query (from TRANSFORM down) to the top of the SQL view editor window, as shown in Illustration 10.

We can see that the crosstab query returns a list of different Customers with product quantities (counts), summarized by product category, for each. As we have noted, the PIVOT is pivoting over Category Name, presenting an individual column for which sales occurred. We also note that there are a total of 89 records.

Let's take a look at restricting the data retrieved with an IN clause addition to the PIVOT clause.

22. Shift back to SQL view, once again, if necessary.

23. Add the following between the end of the existing PIVOT line of the query and the ";" character at its end:

IN ("Produce", "Seafood", Magazines);

NOTE: Be sure to avoid surrounding Magazines with quotation marks in the added clause. We shall see why momentarily.