Cross-tab result sets are a common requirement in business analysis, especially when aggregating totals on a category/time period basis. However, cross-tab result sets can be generated for any two columns in an ElevateDB table, view, or result set, and not just categories or time periods. A cross-tab result set is simply a table of totals where one column is represented via the Y-axis of the table and the other column is represented via the X-axis of the table.

It is fairly simple to create a cross-tab result set from any table, view, or result set with the following structure:

CREATE TABLE CrossTabSourceTable
(
   "Y-Axis Column" <Data Type>
   "X-Axis Column" <Data Type>
   "Total" <Data Type>
)

The key to the whole process is:

1) Get the data into the above structure via a SELECT statement.

2) Proceed to generate the X-Axis columns for the cross-tab result set using:

a) the X-Axis Column values for the X-Axis column names, and
b) the Total values for the X-Axis column values.

To get started with an example of a cross-tab result set script, let's first define a sample database that we can use with the example:

CREATE DATABASE "CrossTabExample"
PATH 'c:\crosstabexample'
DESCRIPTION 'Cross-tab result set example database'

CREATE TABLE "Customer"
(
"No" INTEGER GENERATED ALWAYS AS IDENTITY
   (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(60) COLLATE "ANSI" NOT NULL,
"Address1" VARCHAR(60) COLLATE "ANSI" NOT NULL,
"Address2" VARCHAR(60) COLLATE "ANSI",
"City" VARCHAR(40) COLLATE "ANSI" NOT NULL,
"StateProvince" VARCHAR(40) COLLATE "ANSI" NOT NULL,
"PostalCode" VARCHAR(30) COLLATE "ANSI" NOT NULL,
"Country" VARCHAR(40) COLLATE "ANSI" NOT NULL,
"SalesPersonID" INTEGER NOT NULL,
CONSTRAINT "No" PRIMARY KEY ("No"),
CONSTRAINT "SalesPersonID" FOREIGN KEY ("SalesPersonID")
   REFERENCES "SalesPerson" ("ID")
)

INSERT INTO "Customer" VALUES (1,
                     'Acme Fasteners, Inc.',
                     '100 Main Street',
                     NULL,
                     'Pleasantville',
                     'NY',
                     '99999',
                     'United States',
                     1);

INSERT INTO "Customer" VALUES (2,
                     'Smith Brothers Machinery, Inc.',
                     '200 Cherry Street',
                     NULL,
                     'Orangeland',
                     'CA',
                     '77777',
                     'United States',
                     2);

INSERT INTO "Customer" VALUES (3,
                     'Mister Plow, Inc.',
                     '742 Evergreen Terrace',
                     NULL,
                     'Springfield',
                     'Illinois',
                     '55555',
                     'United States',
                     1);

INSERT INTO "Customer" VALUES (4,
                     'Planet Express, Inc.',
                     '3.14159265 Circle Street',
                     NULL,
                     'New New York',
                     'NY',
                     '3000',
                     'United States',
                     1);

CREATE TABLE "SalesPerson"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY
   (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(40) COLLATE "ANSI" NOT NULL,
CONSTRAINT "ID" PRIMARY KEY ("ID")
)

INSERT INTO "SalesPerson" VALUES (1,
                     'Guy Smiley');

INSERT INTO "SalesPerson" VALUES (2,
                     'Tom Sawyer');

CREATE TABLE "Orders"
(
"CustomerNo" INTEGER NOT NULL,
"OrderNo" INTEGER GENERATED ALWAYS AS IDENTITY
   (START WITH 0, INCREMENT BY 1) NOT NULL,
"OrderDate" DATE NOT NULL,
"OrderTotal" DECIMAL(20,2) DEFAULT 0.00 NOT NULL,
CONSTRAINT "OrderNo" PRIMARY KEY ("OrderNo"),
CONSTRAINT "CustomerNo" FOREIGN KEY ("CustomerNo")
   REFERENCES "Customer" ("No")
)

INSERT INTO "Orders" VALUES (1,
                     1,
                     DATE '2007-05-26',
                     2500);

INSERT INTO "Orders" VALUES (1,
                     2,
                     DATE '2007-06-14',
                     2200);

INSERT INTO "Orders" VALUES (1,
                     3,
                     DATE '2007-09-19',
                     3000);

INSERT INTO "Orders" VALUES (2,
                     4,
                     DATE '2007-01-06',
                     5000);

INSERT INTO "Orders" VALUES (2,
                     5,
                     DATE '2007-03-29',
                     4300);

INSERT INTO "Orders" VALUES (3,
                     6,
                     DATE '2007-10-02',
                     9800);

INSERT INTO "Orders" VALUES (3,
                     7,
                     DATE '2007-12-16',
                     3800);

INSERT INTO "Orders" VALUES (4,
                     8,
                     DATE '2007-07-18',
                     12000);

INSERT INTO "Orders" VALUES (4,
                     9,
                     DATE '2007-04-12',
                     6000);

INSERT INTO "Orders" VALUES (4,
                     10,
                     DATE '2007-04-28',
                     3000);

As you can see, the sample database consists of three tables:


The Customer table contains a foreign key constraint that links each customer to a specific salesperson in the SalesPerson table. The Orders table contains a foreign key constraint that links each order to a specific customer in the Customer table. Using these relationships and the OrderDate value for each order, we can create a summary of the monthly sales for each salesperson, and this is exactly what we will use as the basis for generating the cross-tab result set.

Creating the Summary SELECT Statement

---

In order to complete the first step in the process, we're going to create a SELECT statement that will generate the initial result set that we need in the cross-tab result set script. The SELECT statement is defined as follows:

SELECT '#'+ CAST(SalesPerson.ID AS VARCHAR) +
   ' - ' + SalesPerson.Name AS SalesPerson,
EXTRACT(MONTH FROM OrderDate) AS MonthNo,
SUM(OrderTotal) AS TotalSales
FROM Orders INNER JOIN Customer
ON Customer.No=Orders.CustomerNo
INNER JOIN SalesPerson
ON SalesPerson.ID=Customer.SalesPersonID
WHERE OrderDate BETWEEN ? AND ?
GROUP BY SalesPerson, MonthNo

We are using parameters for the range of order dates, and those will be passed into the SELECT statement from the script parameters themselves, as you will see shortly.

Once the SELECT statement is executed, the result set rows look like the following:



So, now that we've got the SELECT statement for the summary of the sales by salesperson and month, we can begin on the cross-tab result set script.

Creating the Cross-Tab Script

---

Let's quickly revisit our goals in generating the cross-tab result set:

1) We need to summarize the monthly sales per salesperson.

2) We need to see the monthly sales in a table with the salespersons on the Y-axis and the sales totals by month on the X-axis.

3) We need to see all months in the year that we're analyzing, even if there were no orders placed within that month.

4) We need to see the month names, not the month numbers.

To begin with the script, we need to first determine which parameters we'll need to use. For now, let's just pass in the start and end date for the year for which we would like the results.

Our cross-tab result set script initially looks like this:

SCRIPT (IN StartDate DATE, IN EndDate DATE)
BEGIN
END

We've got all of the parameters defined, and now we need to start defining the body of the script. The next thing that we need the script to do is to create a temporary table to use for storing the cross-tab result set. In addition, we'll make sure to drop the table first, if it exists, and suppressing any exceptions if it doesn't.

SCRIPT (IN StartDate DATE, IN EndDate DATE)
BEGIN

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE MonthlySalesCrossTab';
   EXCEPTION
   END;

   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE MonthlySalesCrossTab
(
   SalesPerson VARCHAR(60),
   January DECIMAL(20,2) DEFAULT 0.00,
   February DECIMAL(20,2) DEFAULT 0.00,
   March DECIMAL(20,2) DEFAULT 0.00,
   April DECIMAL(20,2) DEFAULT 0.00,
   May DECIMAL(20,2) DEFAULT 0.00,
   June DECIMAL(20,2) DEFAULT 0.00,
   July DECIMAL(20,2) DEFAULT 0.00,
   August DECIMAL(20,2) DEFAULT 0.00,
   September DECIMAL(20,2) DEFAULT 0.00,
   October DECIMAL(20,2) DEFAULT 0.00,
   November DECIMAL(20,2) DEFAULT 0.00,
   December DECIMAL(20,2) DEFAULT 0.00
)';

END

The next step is to go ahead and DECLARE the cross-tab result set cursor that we'll use for returning the result set on the temporary table that we've created. Let's go ahead and call the result set cursor ResultCursor, and be sure to declare it using the WITH RETURN clause so that the result set will be returned when the script is executed. We want the ResultCursor cursor to be a SENSITIVE cursor so that it can be updated directly later on in the script. The default option for declared cursors is ASENSITIVE, which means that ElevateDB will try to generate a SENSITIVE cursor if it can, and it can in this case since the SELECT statement that we're using is a simple "SELECT ALL" statement.

SCRIPT (IN StartDate DATE, IN EndDate DATE)
BEGIN

   DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;

   UNPREPARE ResultStmt;

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE MonthlySalesCrossTab';
   EXCEPTION
   END;

   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE MonthlySalesCrossTab
(
   SalesPerson VARCHAR(60),
   January DECIMAL(20,2) DEFAULT 0.00,
   February DECIMAL(20,2) DEFAULT 0.00,
   March DECIMAL(20,2) DEFAULT 0.00,
   April DECIMAL(20,2) DEFAULT 0.00,
   May DECIMAL(20,2) DEFAULT 0.00,
   June DECIMAL(20,2) DEFAULT 0.00,
   July DECIMAL(20,2) DEFAULT 0.00,
   August DECIMAL(20,2) DEFAULT 0.00,
   September DECIMAL(20,2) DEFAULT 0.00,
   October DECIMAL(20,2) DEFAULT 0.00,
   November DECIMAL(20,2) DEFAULT 0.00,
   December DECIMAL(20,2) DEFAULT 0.00
)';

   PREPARE ResultStmt FROM 'SELECT * FROM MonthlySalesCrossTab';

   OPEN ResultCursor;

END

The first question that probably came to mind in looking at this last bit of code was "Why the UNPREPARE at the beginning ?". This is a very good question to ask, and the answer is very simple. ElevateDB caches the execution of scripts so that they are prepared once and executed many times. This means that subsequent executions beyond the first execution would result in the script simply using the existing open ResultCursor from the previous execution. Remember, we are returning the ResultCursor as the result set cursor for the script, so we never actually close it. Because the ResultCursor is still open and references the MonthlySalesCrossTab temporary table, we must include the UNPREPARE statement to ensure that the ResultCursor is closed, and that the ResultStmt used by the ResultCursor cursor no longer has any open references to the MonthlySalesCrossTab temporary table. If we didn't do this, the DROP TABLE statement would fail to execute correctly, causing the subsequent CREATE TABLE statement to fail also because the temporary table will already exist.

The next step is to use the summary SELECT statement that we defined earlier in the actual script to generate the summary cursor that we'll use in populating the temporary table that we just created. This will require that we declare a CURSOR variable for navigating the summary result set, which we'll call SummaryCursor in this case.

SCRIPT (IN StartDate DATE, IN EndDate DATE)
BEGIN

   DECLARE SummaryCursor CURSOR FOR SummaryStmt;
   DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;

   UNPREPARE ResultStmt;

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE MonthlySalesCrossTab';
   EXCEPTION
   END;

   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE MonthlySalesCrossTab
(
   SalesPerson VARCHAR(60),
   January DECIMAL(20,2) DEFAULT 0.00,
   February DECIMAL(20,2) DEFAULT 0.00,
   March DECIMAL(20,2) DEFAULT 0.00,
   April DECIMAL(20,2) DEFAULT 0.00,
   May DECIMAL(20,2) DEFAULT 0.00,
   June DECIMAL(20,2) DEFAULT 0.00,
   July DECIMAL(20,2) DEFAULT 0.00,
   August DECIMAL(20,2) DEFAULT 0.00,
   September DECIMAL(20,2) DEFAULT 0.00,
   October DECIMAL(20,2) DEFAULT 0.00,
   November DECIMAL(20,2) DEFAULT 0.00,
   December DECIMAL(20,2) DEFAULT 0.00
)';

   PREPARE ResultStmt FROM 'SELECT * FROM MonthlySalesCrossTab';

   OPEN ResultCursor;

   PREPARE SummaryStmt FROM
'SELECT ''#'' + CAST(SalesPerson.ID AS VARCHAR) +
   '' - '' + SalesPerson.Name AS SalesPerson,
EXTRACT(MONTH FROM OrderDate) AS MonthNo,
SUM(OrderTotal) AS TotalSales
FROM Orders INNER JOIN Customer
ON Customer.No=Orders.CustomerNo
INNER JOIN SalesPerson
ON SalesPerson.ID=Customer.SalesPersonID
WHERE OrderDate BETWEEN ? AND ?
GROUP BY SalesPerson, MonthNo';

   OPEN SummaryCursor USING StartDate, EndDate;

END

So, let's revisit where we're at. We've created a temporary table that reflects the cross-tab format that we want the data to be in when we're done, and we've created the summary result set that gives us the data in a format that we can use to populate the temporary table. The final and missing step is to actually populate the temporary table with the data from the summary result set. This process looks rather complicated, but is really very simple. The steps are as follows:

1) Fetch the first row values from the SummaryCursor cursor into local variables.

2) Begin looping on the SummaryCursor cursor, testing for EOF.

3) Save the current salesperson variable into another variable for grouping purposes.

4) Insert a new row into the ResultCursor cursor, populating only the SalesPerson column.

5) Begin looping on the SummaryCursor cursor again, this time testing for EOF and whether the current salesperson variable matches the saved salesperson variable.

6) Test the month number variable in a CASE statement, and based upon the month number, fetch the current TotalSales column value for the month into a variable and update the ResultCursor cursor so that the TotalSales value is added to any existing value for the month in the ResultCursor cursor.

7) Fetch the next row values from the SummaryCursor cursor into local variables at the bottom of the second loop, and repeat the loop.

That's a lot to take in, so let's look at the completed script code so that we can better understand the mechanisms at work:

SCRIPT (IN StartDate DATE, IN EndDate DATE)
BEGIN

   DECLARE SummaryCursor CURSOR FOR SummaryStmt;
   DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;
   DECLARE CurrentSalesPerson VARCHAR DEFAULT '';
   DECLARE CurrentTotalSales DECIMAL(20,2) DEFAULT 0;
   DECLARE SalesPerson VARCHAR DEFAULT '';
   DECLARE MonthNo INTEGER DEFAULT 0;
   DECLARE MonthName VARCHAR DEFAULT '';
   DECLARE TotalSales DECIMAL(20,2) DEFAULT 0;

   UNPREPARE ResultStmt;

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE MonthlySalesCrossTab';
   EXCEPTION
   END;

   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE MonthlySalesCrossTab
(
   SalesPerson VARCHAR(60),
   January DECIMAL(20,2) DEFAULT 0.00,
   February DECIMAL(20,2) DEFAULT 0.00,
   March DECIMAL(20,2) DEFAULT 0.00,
   April DECIMAL(20,2) DEFAULT 0.00,
   May DECIMAL(20,2) DEFAULT 0.00,
   June DECIMAL(20,2) DEFAULT 0.00,
   July DECIMAL(20,2) DEFAULT 0.00,
   August DECIMAL(20,2) DEFAULT 0.00,
   September DECIMAL(20,2) DEFAULT 0.00,
   October DECIMAL(20,2) DEFAULT 0.00,
   November DECIMAL(20,2) DEFAULT 0.00,
   December DECIMAL(20,2) DEFAULT 0.00
)';

   PREPARE ResultStmt FROM 'SELECT * FROM MonthlySalesCrossTab';

   OPEN ResultCursor;

   PREPARE SummaryStmt FROM
'SELECT ''#'' + CAST(SalesPerson.ID AS VARCHAR) +
   '' - '' + SalesPerson.Name AS SalesPerson,
EXTRACT(MONTH FROM OrderDate) AS MonthNo,
SUM(OrderTotal) AS TotalSales
FROM Orders INNER JOIN Customer
ON Customer.No=Orders.CustomerNo
INNER JOIN SalesPerson
ON SalesPerson.ID=Customer.SalesPersonID
WHERE OrderDate BETWEEN ? AND ?
GROUP BY SalesPerson, MonthNo';

   OPEN SummaryCursor USING StartDate, EndDate;

   FETCH FIRST FROM SummaryCursor INTO SalesPerson, MonthNo, TotalSales;

   WHILE NOT EOF(SummaryCursor) DO
      SET CurrentSalesPerson = SalesPerson;
      INSERT INTO ResultCursor ('SalesPerson') VALUES (SalesPerson);
      WHILE NOT EOF(SummaryCursor) AND (SalesPerson = CurrentSalesPerson) DO
         CASE MonthNo
            WHEN 1 THEN SET MonthName='January';
            WHEN 2 THEN SET MonthName='February';
            WHEN 3 THEN SET MonthName='March';
            WHEN 4 THEN SET MonthName='April';
            WHEN 5 THEN SET MonthName='May';
            WHEN 6 THEN SET MonthName='June';
            WHEN 7 THEN SET MonthName='July';
            WHEN 8 THEN SET MonthName='August';
            WHEN 9 THEN SET MonthName='September';
            WHEN 10 THEN SET MonthName='October';
            WHEN 11 THEN SET MonthName='November';
            WHEN 12 THEN SET MonthName='December';
         END CASE;
         FETCH FROM ResultCursor (MonthName) INTO CurrentTotalSales;
         UPDATE ResultCursor SET MonthName = CurrentTotalSales + TotalSales;
         FETCH NEXT FROM SummaryCursor INTO SalesPerson, MonthNo, TotalSales;
      END WHILE;
   END WHILE;

END

Here is what the cross-tab result set looks like once the script is executed in the ElevateDB Manager:



Hopefully we've been able to give you a little better understanding of one of the many powerful things that you can accomplish using scripts in ElevateDB. As always, if you have any questions at all, please feel free to post them in the ElevateDB newsgroup here:

ElevateDB General Support Forum

This will allow everyone to share in any new ideas or comments that you may have regarding this article.

The next technical article will be entitled "Collations and Comparisons", so look for it soon.

 Source Code for this Article