SQL行转列并分类汇总!

 

create table #temp (employeeName nchar(10),payCategory nchar(12),amount numeric(10,2))

insert into #temp (employeeName,payCategory,amount) values ('王五','吃饭',10.2)

insert into #temp (employeeName,payCategory,amount) values ('王五','洗头',25)

insert into #temp (employeeName,payCategory,amount) values ('王五','座车',2)

insert into #temp (employeeName,payCategory,amount) values ('王五','住店',200)

insert into #temp (employeeName,payCategory,amount) values ('张三','吃饭',20)

insert into #temp (employeeName,payCategory,amount) values ('张三','洗头',50)

insert into #temp (employeeName,payCategory,amount) values ('张三','座车',9)

insert into #temp (employeeName,payCategory,amount) values ('张三','住店',300)

insert into #temp (employeeName,payCategory,amount) values ('张三','住店',500)

select employeeName,[吃饭] as "吃",[洗头] as "洗",[座车] as "车",[住店] as "住" from #temp  pivot(sum(amount) for payCategory in ([吃饭],[洗头],[座车],[住店])) as PVT

drop table #temp

 

结果:

employeeName 吃                洗                车                住                

------------ ---------------- ---------------- ---------------- ---------------- 

王五           10.2             25               2                200              

张三           20               50               9                800              

(9 行受影响)

(返回 2 行)