mysql 日期行转列，请教各位SQL大神

Question

SQL菜鸟，还是行转列的问题，不过是按照日期来。baidu、google了各种资料没实现，为此才发贴，请教各位 SQL 高手 ！/************分割线*************//*----用户注册表----*/
CREATE TABLE `reg_user` (
`id` DOUBLE ,
`userid` DOUBLE ,
`username` VARCHAR(20),
`ag_id` INT ,
`reg_time` VARCHAR(20) 
); 
INSERT INTO `reg_user`  VALUES('1','1001','aaa1001','2','2012-09-01');
INSERT INTO `reg_user`  VALUES('2','1002','aaa1002','3','2012-09-01');
INSERT INTO `reg_user`  VALUES('3','1003','aaa1003','6','2012-09-01');INSERT INTO `reg_user`  VALUES('4','1004','aaa1004','1','2012-09-02');
INSERT INTO `reg_user`  VALUES('5','1005','aaa1005','1','2012-09-02');
INSERT INTO `reg_user`  VALUES('6','1006','aaa1006','3','2012-09-02');INSERT INTO `reg_user`  VALUES('7','1007','aaa1007','6','2012-09-03');
INSERT INTO `reg_user`  VALUES('8','1008','aaa1008','3','2012-09-03');
INSERT INTO `reg_user`  VALUES('9','1009','aaa1009','1','2012-09-03');INSERT INTO `reg_user`  VALUES('10','1010','aaa1010','3','2012-09-20');
INSERT INTO `reg_user`  VALUES('11','1011','aaa1011','3','2012-09-20');
INSERT INTO `reg_user`  VALUES('12','1012','aaa1012','1','2012-09-20');INSERT INTO `reg_user`  VALUES('13','1013','aaa1013','2','2012-09-21');
INSERT INTO `reg_user`  VALUES('14','1014','aaa1014','2','2012-09-21');
INSERT INTO `reg_user`  VALUES('15','1015','aaa1015','3','2012-09-21');/*----广告商----*/
CREATE TABLE `advertisers` (
`ag_id` DOUBLE ,
`ag_name` VARCHAR (96)
); 
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('1','广告1');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('2','广告2');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('3','广告3');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('4','广告4');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('5','广告5');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('6','广告6');/*  想查询出的结果为：根据每个广告商统计每天的注册人数(30天)，*/广告商   2012-09-01  2012-09-02 2012-09-03 ... ... 2012-09-20 2012-09-21 ... 2012-09-30
广告1   0 2 1 ... ... 1 0 0      
广告2   1 0 0 ... ... 0 2 0   
广告3   1 1 1 ... ... 2 1 0   
广告4   0 0 0 ... ... 0 0 0   
广告5   0 0 0 ... ... 0 0 0   
广告6   1 0 1 ... ... 0 0 0   

Answer 1

mysql的我不会，既然你发到SQLServer专区，那我给个SQLServer的写法给你自己改吧/*----用户注册表----*/
 CREATE TABLE reg_user (
     id int ,
     userid int ,
     username VARCHAR(20),
     ag_id INT ,
     reg_time VARCHAR(20) 
 ); 
 INSERT INTO reg_user  VALUES('1','1001','aaa1001','2','2012-09-01');
 INSERT INTO reg_user  VALUES('2','1002','aaa1002','3','2012-09-01');
 INSERT INTO reg_user  VALUES('3','1003','aaa1003','6','2012-09-01');
 
 INSERT INTO reg_user  VALUES('4','1004','aaa1004','1','2012-09-02');
 INSERT INTO reg_user  VALUES('5','1005','aaa1005','1','2012-09-02');
 INSERT INTO reg_user  VALUES('6','1006','aaa1006','3','2012-09-02');
 
 INSERT INTO reg_user  VALUES('7','1007','aaa1007','6','2012-09-03');
 INSERT INTO reg_user  VALUES('8','1008','aaa1008','3','2012-09-03');
 INSERT INTO reg_user  VALUES('9','1009','aaa1009','1','2012-09-03');
 
 INSERT INTO reg_user  VALUES('10','1010','aaa1010','3','2012-09-20');
 INSERT INTO reg_user  VALUES('11','1011','aaa1011','3','2012-09-20');
 INSERT INTO reg_user  VALUES('12','1012','aaa1012','1','2012-09-20');
 
 INSERT INTO reg_user  VALUES('13','1013','aaa1013','2','2012-09-21');
 INSERT INTO reg_user  VALUES('14','1014','aaa1014','2','2012-09-21');
 INSERT INTO reg_user  VALUES('15','1015','aaa1015','3','2012-09-21');
 
 /*----广告商----*/
 CREATE TABLE advertisers (
     ag_id int ,
     ag_name VARCHAR (96)
 ); 
 INSERT INTO advertisers   VALUES('1','广告1');
 INSERT INTO advertisers   VALUES('2','广告2');
 INSERT INTO advertisers   VALUES('3','广告3');
 INSERT INTO advertisers   VALUES('4','广告4');
 INSERT INTO advertisers   VALUES('5','广告5');
 INSERT INTO advertisers   VALUES('6','广告6');
 SELECT * FROM #t
 
 SELECT a.id,a.userid,a.username,a.reg_time,b.ag_name INTO t
 FROM reg_user a INNER JOIN advertisers b ON a.ag_id=b.ag_id
 
 declare @s nvarchar(4000)
 set @s=''
 Select     @s=@s+','+quotename(reg_time)+'=count(case when [reg_time]='+quotename(reg_time,'''')+' then [ag_name] else null end)'
 from t group BY ag_name,reg_time
 --PRINT @s
 EXEC ('select [ag_name]'+@s+',[reg_time]=count(ag_name)  from t group by ag_name')
 
 select [ag_name],[2012-09-02]=count(case when [reg_time]='2012-09-02' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[2012-09-20]=count(case when [reg_time]='2012-09-20' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-21]=count(case when [reg_time]='2012-09-21' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-02]=count(case when [reg_time]='2012-09-02' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[2012-09-20]=count(case when [reg_time]='2012-09-20' then [ag_name] else null end),[2012-09-21]=count(case when [reg_time]='2012-09-21' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[reg_time]=count(ag_name)  from t group by ag_name
 
 /*
 ag_name                                                                                          2012-09-02  2012-09-03  2012-09-20  2012-09-01  2012-09-21  2012-09-01  2012-09-02  2012-09-03  2012-09-20  2012-09-21  2012-09-01  2012-09-03  reg_time
 ------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
 广告1                                                                                              2           1           1           0           0           0           2           1           1           0           0           1           4
 广告2                                                                                              0           0           0           1           2           1           0           0           0           2           1           0           3
 广告3                                                                                              1           1           2           1           1           1           1           1           2           1           1           1           6
 广告6                                                                                              0           1           0           1           0           1           0           1           0           0           1           1           2
 警告: 聚合或其他 SET 操作消除了 Null 值。
 
 (4 行受影响)
 
 
 */