SQL查询行转列和列转行

行转列

主要思路是分组后使用case进行条件判断处理

示例1

select team,
	sum(case when prjsts='DEV' then prjcnt else 0 end) as 'DEV',
	sum(case  when prjsts in('STS','STE','UTS') then prjcnt else 0 end) as 'TST',
	sum(case when prjsts='PDS' then prjcnt else 0 end) as 'PDS',
    sum(case when prjsts='PAU' then prjcnt else 0 end) as 'PAU'
from (
	select a.prjsts,b.Team, 1 as prjcnt
	from kkprjinf a,fbusers b
	where a.teamld=b.id)as rs
group by (team);

示例2

create table wyc_test(
	id int(32) not null auto_increment,
	name varchar(80) default null,
	date date default null,
	scount int(32),
	primary key (id)
);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小说','2013-09-01',10000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','2013-09-02',30000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','2013-09-03',31000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','2013-09-04',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','2013-09-01',80000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);

#行转列
select 
    a.date,
    sum(case a.name
        when '小说' then a.scount
        else 0
    end) 'sum_小说',
    max(case a.name
        when '小说' then a.scount
        else 0
    end) 'max_小说',
    sum(case a.name
        when '微信' then a.scount
        else 0
    end) '微信',
    max(case a.name
        when '小说' then a.scount
        else 0
    end) 'max_微信'
from
    wyc_test a
group by date;

结果