on 2010 Jan 11 7:22 AM
This question and answer is copied from the SQL Anywhere newsgroup AND a post to be published later this morning: http://sqlanywhere.blogspot.com/2010/01/crosstab-rotate-pivot.html
Question: How do I rotate a table so that different row values in a single column become different columns in a new table?
SQL Anywhere doesn't have the ability to rotate (crosstab, pivot, whatever) a table. What it does have is EXECUTE IMMEDIATE, plus the ability to code IF expressions just about anywhere in the SELECT statement. You can combine those two features to kludge a solution.
It's hard to explain what rotating a table is all about, it's not exactly as simple as turning rows into columns and vice versa... it's easier to use an example.
Here's a table showing sales by state and quarter:
-- Part 1: Initialize data. BEGIN DROP TABLE t1; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE t1 ( c1 VARCHAR ( 10 ) NOT NULL, c2 VARCHAR ( 10 ) NOT NULL, c3 INTEGER 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; SELECT * FROM t1 ORDER BY c1, c2;
So far, so good... the table is nicely normalized, everything's elegant... and useless:
c1 c2 c3 AZ Q1 5000 AZ Q2 5000 AZ Q3 1000 AZ Q4 3000 CA Q1 1000 CA Q2 2000 CA Q3 9000 CA Q4 7000 FL Q1 9000 FL Q2 7000 FL Q3 2000 FL Q4 1000 MA Q1 2000 MA Q2 6000 MA Q3 5000 MA Q4 3000 NY Q1 4000 NY Q2 5000 NY Q3 1000 NY Q4 6000
What folks often want to see is something like this, sales by quarter for each state:
c2 AZ CA FL MA NY Q1 5000 1000 9000 2000 4000 Q2 5000 2000 7000 6000 5000 Q3 1000 9000 2000 5000 1000 Q4 3000 7000 1000 3000 6000
Here's how you can do that in SQL Anywhere:
-- 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 END;
What if you want to see sales by state for each quarter?
c1 Q1 Q2 Q3 Q4 AZ 5000 5000 1000 3000 CA 1000 2000 9000 7000 FL 9000 7000 2000 1000 MA 2000 6000 5000 3000 NY 4000 5000 1000 6000
Here's the code for that:
-- 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 END;
Here's where the magic lies, in the SUMs of row values multiplied by 1 or 0; the following SELECT statements are generated by the code shown above:
SELECT c2, SUM ( ( IF t1.c1 = 'AZ' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "AZ", SUM ( ( IF t1.c1 = 'CA' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "CA", SUM ( ( IF t1.c1 = 'FL' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "FL", SUM ( ( IF t1.c1 = 'MA' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "MA", SUM ( ( IF t1.c1 = 'NY' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "NY" INTO #t1 FROM t1 GROUP BY c2 SELECT c1, SUM ( ( IF t1.c2 = 'Q1' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q1", SUM ( ( IF t1.c2 = 'Q2' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q2", SUM ( ( IF t1.c2 = 'Q3' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q3", SUM ( ( IF t1.c2 = 'Q4' THEN 1 ELSE 0 ENDIF ) * t1.c3 ) AS "Q4" INTO #t1 FROM t1 GROUP BY c1
The "SUM IF 1 OR 0" trick is an old one, used by generations of programmers to write funky code for icky problems, and not just in SQL.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Great example Breck. I'll note that you can likely get a slight performance improvement if you remove the multiplication by t1.c3 and move the value into the THEN clause: E.g. SUM( IF t1.c1 = 'Q1' THEN t1.c3 ELSE 0 ENDIF ). It likely won't make a whole lot of difference, but everything helps.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.