转置并汇总
请问各位大师，转置并汇总应该如何写SQL，例子如下：
人员休假类别天数
A X 1
A X 0.5
A Y 1
A Y 1
A Y 1
A Z 0.5
A Z 0.5
A Z 1
B X 1
B X 1
B Y 0.5
B Y 0.5
B Y 1
B Z 0.5
B Z 0.5
B Z 0.5
想得到的结果如下：
人员休假X休假Y休假Z
A 1.5 3 2
B 2 2 1.5
转置并汇总
[解决办法]
create table tab8
(
pople varchar(50),
type varchar(50),
num varchar(50)
)
insert into tab8
select 'A','X','1' union all
select 'A','X', '0.5' union all
select 'A','Y', '1' union all
select 'A','Y', '1' union all
select 'A','Y', '1' union all
select 'A','Z', '0.5' union all
select 'A','Z', '0.5' union all
select 'A','Z', '1' union all
select 'B','X', '1' union all
select 'B','X', '1' union all
select 'B','Y', '0.5' union all
select 'B','Y', '0.5' union all
select 'B','Y', '1' union all
select 'B','Z', '0.5' union all
select 'B','Z', '0.5' union all
select 'B','Z', '0.5'
select * from tab8
select
pople 人员
--通过Case When 实现行转列功能
,sum(case type when 'x' then convert(float,num) else 0 end) as 休假x
,sum(case type when 'y' then convert(float,num) else 0 end) as 休假y
,sum(case type when 'z' then convert(float,num) else 0 end) as 休假z
from tab8
group by pople
人员 休假x 休假y 休假z
-------------------------------------------------- ---------------------- ---------------------- ----------------------
A 1.5 3 2
B 2 2 1.5
(2 行受影响)
