Rotate SQL tables

Posted Sun, 23/11/2008 - 17:44 by mark
, , ,

 I need rotate a relative big table (~100K rows)

The source table:

id env date
1 yahoo 1/1/3
2 igoogle 1/1/4
3 vista 1/2/4
4 igoogle 1/1/2
5 iphone 12/12/02

 

Target table or query result set must be:

    iphone igoogle yahoo vista
    1 2 1 1

 

I need (in single SELECT) count rows grouping by 'env' and create result as shown before.

Any ideas?

 

‹ JBoss Seam - Multiple WARs under single EAR - HOW ? Can one applet load another applet on runtime, and have both applets in the same page? ›

Comments

by lior.kanfi - 25/11/2008 - 01:14

what your are looking for is crosstab, for instance see this example at mysql
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

by tomer - 25/11/2008 - 12:50

If you know the environment types in advanced,
you can simply use:

insert into SomeTable values (
(select count(1) from mytbl where env = 'iphone'),
(select count(1) from mytbl where env = 'igoogle'),
(select count(1) from mytbl where env = 'yahoo'),
(select count(1) from mytbl where env = 'vista')
);

by mark - 25/11/2008 - 12:59

Now I use select shown below :
What is your opinion is it lightweight for the large tables (100K - 1M) rows

select
igoogle.count as igoogle,
googleDesktop.count as googleDesktop,
yahoo.count as yahoo,
iphone.count as iphone,
vista.count as vista
from
(select count(ENVIRONMENT) as count from mytable where environment = 'igoogle' and time < ?) igoogle,
(select count(ENVIRONMENT) as count from mytable where environment = 'google-desktop' and time < ?) googleDesktop,
(select count(ENVIRONMENT) as count from mytable where environment = 'yahoo' and time < ?) yahoo,
(select count(ENVIRONMENT) as count from mytable where environment = 'iphone' and time < ?) iphone,
(select count(ENVIRONMENT) as count from mytable where environment = 'vista' and time < ?) vista