Adam,

<< Thanks for your offer. I have sent you an email. >>

Okay, this is your original SQL:

Transform max(Price)
Select Product
From Pricing
Group by Product
Order by Product
Pivot (Supplier)

This is the table creation code:

CREATE TABLE "Pricing"
(
"Product" VARCHAR(30) COLLATE "ANSI",
"Supplier" VARCHAR(30) COLLATE "ANSI",
"Price" DECIMAL(19,2)
)

Here are the rows:

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-100',
                 'TEST-VENDOR-SMITHBROS',
                 10)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-100',
                 'TEST-VENDOR-GENTECH',
                 11)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-100',
                 'TEST-VENDOR-CULLIN',
                 10.5)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-100',
                 'TEST-VENDOR-BOYD',
                 12.2)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-100',
                 'TEST-VENDOR-ANDERSON',
                 13)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-200',
                 'TEST-VENDOR-SMITHBROS',
                 50)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-200',
                 'TEST-VENDOR-GENTECH',
                 45)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-200',
                 'TEST-VENDOR-CULLIN',
                 48.75)!

INSERT INTO "Pricing" VALUES ('TEST-PRODUCT-300',
                 'TEST-VENDOR-SMITHBROS',
                 18.6)!

And this is the script:

SCRIPT (IN TableName VARCHAR, IN YColumnName VARCHAR, IN XColumnName
VARCHAR,
       IN DataColumnName VARCHAR, IN OperationType INTEGER, IN
ResultTableName VARCHAR)
BEGIN

  DECLARE TableSQL VARCHAR DEFAULT '';
  DECLARE XColumnData VARCHAR DEFAULT '';
  DECLARE NumXColumns INTEGER DEFAULT 0;
  DECLARE YColumnData VARCHAR DEFAULT '';
  DECLARE CurYColumnData VARCHAR DEFAULT '';
  DECLARE ColumnData DECIMAL(20,2) DEFAULT 0;
  DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;
  DECLARE SummarySQL VARCHAR DEFAULT '';
  DECLARE SummaryCursor CURSOR FOR SummaryStmt;
  DECLARE OperationName VARCHAR DEFAULT '';

  SET TableSQL = 'CREATE TEMPORARY TABLE ' + QUOTEDSTR(ResultTableName,'"')
+ '(' +
                 QUOTEDSTR(YColumnName,'"') + ' VARCHAR(30)'; -- Hard-coded
column length

  PREPARE ResultStmt FROM 'SELECT DISTINCT ' + QUOTEDSTR(XColumnName,'"') +
                          ' FROM ' + QUOTEDSTR(TableName,'"') +
                          ' ORDER BY ' + QUOTEDSTR(XColumnName,'"');
  OPEN ResultCursor;

  FETCH FIRST FROM ResultCursor INTO XColumnData;

  WHILE NOT EOF(ResultCursor) DO
     SET TableSQL = TableSQL + ', ' + QUOTEDSTR(XColumnData,'"') + '
DECIMAL(20,2)';
     SET NumXColumns = NumXColumns + 1;
     IF NumXColumns > 512 THEN
        LEAVE;
     END IF;
     FETCH NEXT FROM ResultCursor INTO XColumnData;
  END WHILE;

  SET TableSQL = TableSQL + ')';

  BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE ' + QUOTEDSTR(ResultTableName,'"');
  EXCEPTION
  END;

  EXECUTE IMMEDIATE TableSQL;

  PREPARE ResultStmt FROM 'SELECT * FROM ' +
QUOTEDSTR(ResultTableName,'"');
  OPEN ResultCursor;

  SET SummarySQL = 'SELECT ' + QUOTEDSTR(YColumnName,'"') + ', ' +
                               QUOTEDSTR(XColumnName,'"') + ', ';

  CASE COALESCE(OperationType,0)
     WHEN 0 THEN SET OperationName = 'SUM';
     WHEN 1 THEN SET OperationName = 'MIN';
     WHEN 2 THEN SET OperationName = 'MAX';
  END CASE;

  SET SummarySQL = SummarySQL + OperationName +
'('+QUOTEDSTR(DataColumnName,'"') + ') AS OperationResult' +
                            ' FROM ' + TableName +
                            ' GROUP BY ' + QUOTEDSTR(YColumnName,'"') + ',
' + QUOTEDSTR(XColumnName,'"');

  PREPARE SummaryStmt FROM SummarySQL;
  OPEN SummaryCursor;

  FETCH FIRST FROM SummaryCursor INTO YColumnData, XColumnData, ColumnData;

  WHILE NOT EOF(SummaryCursor) DO
     SET CurYColumnData = YColumnData;
     INSERT INTO ResultCursor (YColumnName) VALUES (YColumnData);
     WHILE NOT EOF(SummaryCursor) AND (YColumnData = CurYColumnData) DO
        UPDATE ResultCursor SET XColumnData = ColumnData;
        FETCH NEXT FROM SummaryCursor INTO YColumnData, XColumnData,
ColumnData;
     END WHILE;
  END WHILE;

END

There are some hard-coded assumptions in there that could be removed, but
they're pretty minor.  The bottom line is that if you want to do a cross-tab
on two VARCHAR columns and aggregate a DECIMAL column value in the process
(SUM, MIN, MAX), this script will do it and generate the indicated resultant
temporary table.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks very much for the example. I'll see what we can do about
implementing this into our current application.

Definitely a lot more work involved than a simple Pivot SQL - however it
can be done completely 'in house' without the requirement of any 3rd
components and I've got the results in a TDataset which is exactly what
I'm looking for!

Thanks you!

Adam