Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/mwexler/public_html/tp/textpattern/lib/txplib_db.php on line 14
The Net Takeaway: Transpose in SQL

OTHER PLACES OF INTEREST

Danny Flamberg's Blog
Danny has been marketing for a while, and his articles and work reflect great understanding of data driven marketing.

Eric Peterson the Demystifier
Eric gets metrics, analytics, interactive, and the real world. His advice is worth taking...

Geeking with Greg
Greg Linden created Amazon's recommendation system, so imagine what can write about...

Ned Batchelder's Blog
Ned just finds and writes interesting things. I don't know how he does it.

R at LoyaltyMatrix
Jim Porzak tells of his real-life use of R for marketing analysis.

 

HOW DID YOU GET HERE?

https:
https:
https:
google.com
https:
google.com
https:
https:
https:
https:

 

 

 

Transpose in SQL · 04/26/2007 01:11 AM, Database

Publishing so I don’t forget:

Assume your data is this:
Year Quarter Amount
2001 1 100
2001 2 115
2001 3 400
2001 4 550
2002 1 200
2002 2 415
2002 3 300
2002 4 750

And you want to wind up with
Year Q1 Q2 Q3 Q4
2001 100 115 400 550
2002 200 415 300 750

Try this:


 SELECT Year,
    MAX(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
    MAX(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
    MAX(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
    MAX(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
 FROM table1
 GROUP BY Year

AKA a “pivot” query.

In effect, you are expanding out the rows, 1 additional row per column with just that column’s info, then “compressing” them with the group by. The MAX is just MAXing 1 real value and a bunch of NULLS, so you wind up with that 1 value again.

This is still manual; you could write a script in PL/SQL or TransactSQL or whatever to dynamically build the appropriate query inside the database and execute it into a new table.

See http://blogs.ittoolbox.com/database/technology/archives/pivot-query-12757 for a slightly fuller explanation of this approach. AskTom, SQLServer Magazine, and other sources have also examined database specific versions of this, but the above gives the basic idea.

(BTW, he also provides an “unpivot” query:
http://blogs.ittoolbox.com/database/technology/archives/unpivot-query-12798)

* * *

 

Name
E-mail
http://
Message
  Textile Help
Please note that your email will be obfuscated via entities, so its ok to put a real one if you feel like it...

Admin
powered by Textpattern 4.0.4 (r1956)