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%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

retagged 12 Nov '13, 13:06

Nica%20_SAP's gravatar image

Nica _SAP
866722

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 :)

(11 Jan '10, 07:36) Breck Carter

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.

  • Is "kludge" too strong a word? Maybe so, since performance is pretty good. But, no question... the code's funky.

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.

permanent link

answered 11 Jan '10, 07:26

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(11 Jan '10, 13:09) Mark Culp

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 )

(11 Jan '10, 13:12) Mark Culp

Just used this a few days ago. Thanks!

(21 May '10, 21:52) Volker Barth

Just used this last week. Thank you!

(20 Sep '10, 13:32) Siger Matt

I just checked the big green "answered" arrow... dya think waiting 9 months is long enough? :)

(20 Sep '10, 14:35) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×34
×19
×13
×2

question asked: 11 Jan '10, 07:22

question was seen: 10,028 times

last updated: 12 Nov '13, 13:06