典型的行转列

create table test(company_name varchar(20),act varchar(10),pagecount int);
insert into test
VALUES('CompanyA','print',3),('CompanyA','print',2 ),('CompanyA','print',3)
,('CompanyB','print',2),('CompanyB','print',2),('CompanyB','print',1),('CompanyA','print',3);

INSERT INTO TEST(company_name,act)
values('CompanyB','EMAIL');


select company_name,sum(email) as EMAIL,
sum(PRINT_1_PAGES) as PRINT_1_PAGES,
sum(PRINT_2_PAGES) as PRINT_2_PAGES,
sum(PRINT_3_PAGES) as PRINT_3_PAGES
from
(
select company_name,case when act = 'EMAIL' THEN 1 ELSE 0 END AS EMAIL,
CASE WHEN ACT = 'print' AND PAGECOUNT =1 THEN 1 ELSE 0 END AS  PRINT_1_PAGES,
CASE WHEN ACT = 'print' AND PAGECOUNT =2 THEN 1 ELSE 0 END AS  PRINT_2_PAGES,
CASE WHEN ACT = 'print' AND PAGECOUNT =3 THEN 1 ELSE 0 END AS  PRINT_3_PAGES
FROM TEST
) as t1
group by company_name

复制

结果

CompanyA 0 0 1 3
CompanyB 1 1 2 0