SQL 列转行的实现

-- =============================================
-- Author: 特工绍特 
-- Create date: 9月19日
-- Description: SQL列转行的实现,列外扩展了一下求和
-- QQ:707891607 欢迎大家来一起交流
-- =============================================

create table #test
(
  id int primary key identity(1,1),
  name varchar(20),
  subject varchar(20),
  source int
)

insert into #test
select '张三','数学','85' union all
select '张三','语文','45' union all
select '张三','英语','80' union all
select '李四','语文','55' union all
select '李四','数学','60' union all
select '王五','数学','65' union all
select '王五','英语','70'

select * from #test
--结果
--id          name                 subject              source
------------- -------------------- -------------------- -----------
--1           张三                   数学                   85
--2           张三                   语文                   45
--3           张三                   英语                   80
--4           李四                   语文                   55
--5           李四                   数学                   60
--6           王五                   数学                   65
--7           王五                   英语                   70

 

select name
,sum(case subject when '数学' then source else 0 end)as '数学'
,sum(case subject when '语文' then source else 0 end)as '语文'
,sum(case subject when '英语' then source else 0 end)as '英语'
,sum(source)as '总分'
from #test group by name
union all
select null
,sum(case subject when '数学' then source else 0 end)as '数学'
,sum(case subject when '语文' then source else 0 end)as '语文'
,sum(case subject when '英语' then source else 0 end)as '英语'
,sum(source)
from #test
--结果
--name                 数学          语文          英语      总分
---------------------- ----------- ----------- ----------- ----------
--李四                   60          55          0           115
--王五                   65          0           70          135
--张三                   85          45          80          210
--NULL                   210         100         150         460