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? asked 11 Jan '10, 07:22 Breck Carter Nica _SAP |
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. answered 11 Jan '10, 07:26 Breck Carter 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. It should likely also be noted that non-numeric (i.e. character) data can also be rotated by replacing SUM with another aggregate function such as LIST(). E.g. if t1.c3 was a CHAR type, then write the expression as LIST( IF t1.c2 = 'Q1' THEN t1.c3 ELSE NULL ENDIF ) Just used this a few days ago. Thanks! Just used this last week. Thank you! I just checked the big green "answered" arrow... dya think waiting 9 months is long enough? :) |
Meta comment: It's not plagiarism, or even self-plagiarism, if you give credit and don't claim it as your own original work. In fact, SQLA encourages people to copy existing material... it's (going to be) easier to find stuff in SQLA than in other places, and that's the second reason SQLA exists ( the first reason, of course, is to answer questions :)