Advanced Crosstab Query

				
--**************************************
-- Name: Advanced Crosstab Query
-- Description:halo friends, i have some scripts for you.
the case like this, for example,
you have 2 tables, students and countries.
students have id, name, year of start and country id, 
countries have id and name.
if you wanna to see the students distribution group by country name,
and the statistics of year, you can use the crosstab query i create.
it will return the result like the screenshot. ou can altering the query
to view the statistics from any year, like this:
(if u use MySQL):
select country_name,
sum(if(year_of_start=2000,1,0)) AS '2000',
sum(if(year_of_start=2001,1,0)) AS '2001',
sum(if(year_of_start=2002,1,0)) AS '2002',
sum(if(year_of_start=2003,1,0)) AS '2003',
sum(if(year_of_start=2004,1,0)) AS '2004'
from students 
left join countries
on students.country_id=countries.country_id
group by country_name;
if you use Access, you must replace "if" statement with "iif"
but if you use SQL Server, like this:
select country_name,
sum( case year_of_start WHEN 2000 THEN 1 END) AS '2000',
sum( case year_of_start WHEN 2001 THEN 1 END) AS '2001',
sum( case year_of_start WHEN 2002 THEN 1 END) AS '2002',
sum( case year_of_start WHEN 2003 THEN 1 END) AS '2003',
from students 
left join countries
on students.country_id=countries.country_id
group by country_name;
-- By: Sukrisno
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1123&lngWId=5--for details.--**************************************

create table students
(
student_id char(10) primary key,
name varchar(25),
year_of_start int(4),
country_id int(2)
);
create table countries 
(
country_id int(2) primary key,
country_name varchar(25)
);
insert into countries values (1,'Pemangkat');
insert into countries values (2,'Sambas');
insert into countries values (3,'Singkawang');
insert into students values('03.01.1698','Sukrisno',2000,1);
insert into students values('03.01.1699','Samuel',2000,1);
insert into students values('03.01.1700','Johny',2000,2);
insert into students values('03.01.1701','James',2000,3);
insert into students values('03.02.1800','Jacques',2001,1);
insert into students values('03.02.1801','Harry',2001,2);
insert into students values('03.02.1802','Wardati',2001,3);
insert into students values('03.03.1901','Jenny',2002,2);
insert into students values('03.03.1902','Evita',2002,2);
insert into students values('03.03.1903','Rahayu',2002,3);
select country_name,
sum(if(year_of_start=2000,1,0)) AS '2000',
sum(if(year_of_start=2001,1,0)) AS '2001',
sum(if(year_of_start=2002,1,0)) AS '2002'
from students 
left join countries
on students.country_id=countries.country_id
group by country_name;