ROTATE A TABLE

[MGS]

Hi all,

I have a table1 with 4 columns: product_id, color, size and
quantity. I have as many rows as different combinations of
products, colors and sizes. I would like to ROTATE the
table to get table2 with N columns: product_id, color,
size_01, quantity_01, size_02, quantity_02, etc. N = 2 x
number of existing different sizes + 2. Then UPDATE some
data and do the inverse process, I mean from table2 to table
1. I know that in some databases there is a PIVOT, UNPIVOT
instructions. Please, how can achieve this in SQL Anywhere
11?

Thanks a lot in advance

[Breck Carter [TeamSybase]]

The pivot/crosstab/rotate function does not exist in SQL Anywhere and
it's not coming in Version 12 either.

It is possible to use a combination of SUM ( IF ... ) and EXECUTE
IMMEDIATE to kludge a solution (see simple examples below, I have more
interesting examples if you want).

FWIW it may *look* kludgy but it runs like a rocket; the Foxhound
database monitor uses this technique on-the-fly when analyzing
realtime statistics gathered from thousands of connections.

Breck

-- Part 1: Initialize data.

IF EXISTS ( SELECT *
FROM SYS.SYSTABLE
WHERE USER_NAME ( SYS.SYSTABLE.creator ) = CURRENT USER
AND SYS.SYSTABLE.table_name = 't1' ) THEN
DROP TABLE t1;
END IF;

CREATE TABLE t1 (
c1 VARCHAR ( 10 ) NOT NULL,
c2 VARCHAR ( 10 ) NOT NULL,
c3 VARCHAR ( 10 ) NOT NULL,
PRIMARY KEY ( c1, c2 ) );

INSERT t1 VALUES ( 'CA', 'Q1', 1000 );
INSERT t1 VALUES ( 'CA', 'Q2', 2000 );
INSERT t1 VALUES ( 'CA', 'Q3', 9000 );
INSERT t1 VALUES ( 'CA', 'Q4', 7000 );

INSERT t1 VALUES ( 'NY', 'Q1', 4000 );
INSERT t1 VALUES ( 'NY', 'Q2', 5000 );
INSERT t1 VALUES ( 'NY', 'Q3', 1000 );
INSERT t1 VALUES ( 'NY', 'Q4', 6000 );

INSERT t1 VALUES ( 'FL', 'Q1', 9000 );
INSERT t1 VALUES ( 'FL', 'Q2', 7000 );
INSERT t1 VALUES ( 'FL', 'Q3', 2000 );
INSERT t1 VALUES ( 'FL', 'Q4', 1000 );

INSERT t1 VALUES ( 'AZ', 'Q1', 5000 );
INSERT t1 VALUES ( 'AZ', 'Q2', 5000 );
INSERT t1 VALUES ( 'AZ', 'Q3', 1000 );
INSERT t1 VALUES ( 'AZ', 'Q4', 3000 );

INSERT t1 VALUES ( 'MA', 'Q1', 2000 );
INSERT t1 VALUES ( 'MA', 'Q2', 6000 );
INSERT t1 VALUES ( 'MA', 'Q3', 5000 );
INSERT t1 VALUES ( 'MA', 'Q4', 3000 );
COMMIT;

-- Part 2: Pivot c1 values into columns.

BEGIN
DECLARE @sql LONG VARCHAR;
SET @sql = 'SELECT c2';
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR
SELECT DISTINCT t1.c1 AS @c1
FROM t1
ORDER BY t1.c1
FOR READ ONLY
DO
SET @sql = STRING (
@sql,
', SUM ( ( IF t1.c1 = ''',
@c1,
''' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "',
@c1,
'"' );
END FOR;
SET @sql = STRING (
@sql,
' INTO #t1 FROM t1 GROUP BY c2' );
MESSAGE @sql TO CONSOLE;
EXECUTE IMMEDIATE @sql;
SELECT * FROM #t1 ORDER BY c2; -- pivot table
SELECT * FROM t1 ORDER BY c1, c2; -- original data, for checking
END;

-- Part 3: Pivot c2 values into columns.

BEGIN
DECLARE @sql LONG VARCHAR;
SET @sql = 'SELECT c1';
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR
SELECT DISTINCT t1.c2 AS @c2
FROM t1
ORDER BY t1.c2
FOR READ ONLY
DO
SET @sql = STRING (
@sql,
', SUM ( ( IF t1.c2 = ''',
@c2,
''' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "',
@c2,
'"' );
END FOR;
SET @sql = STRING (
@sql,
' INTO #t1 FROM t1 GROUP BY c1' );
MESSAGE @sql TO CONSOLE;
EXECUTE IMMEDIATE @sql;
SELECT * FROM #t1 ORDER BY c1; -- pivot table
SELECT * FROM t1 ORDER BY c2, c1; -- original data, for checking
END;

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

[MGS]

Hi Breck,

Your example is good enough to solve the problem I had.

Thanks a lot again.