SQL行转列

一张表

查询结果为

--行转列
select  years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,
(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,
(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3
from  Tb_Amount group by  years

或者为
select years as 年份,
sum(case when month='1' then amount end) as 一月,
 sum(case when month='2' then amount end) as 二月,
sum(case when month='3' then amount end) as 三月
from dbo.Tb_Amount group by years  order by  years desc

 

2.人员信息表包括姓名 时代  金额

显示行转列
姓名     时代       金额

姓名  年轻         中年       老年

张丽 1000000.00 4000000.00    500000000.00

孙子 2000000.00   12233335.00  4552220010.00

select uname as 姓名,
SUM(case when era='年轻' then amount end) as 年轻,
 SUM(case when era='中年' then amount end) as 中年,
SUM(case when era='老年' then amount end) as 老年
from Tb_People group by uname  order by  uname desc

 3.学生表 [Tb_Student]

 

显示效果

静态SQL,指subject只有语文、数学、英语这三门课程。

select sname as 姓名,
max(case Subject when '语文' then grade else 0 end) as 语文,
max(case Subject when '数学' then grade else 0 end) as 数学,
max(case Subject when '英语' then grade else 0 end) as 英语
from dbo.Tb_Student  group by  sname  order by sname desc

--动态SQL,指subject不止语文、数学、英语这三门课程。

declare @sql varchar(8000)
set @sql = 'select sname as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then grade else 0 end) [' + Subject + ']'
from (select distinct Subject from Tb_Student) as a
set @sql = @sql + ' from Tb_Student group by sname order by sname desc'
exec(@sql)

 

oracle中Decode()函数使用 然后将这些累计求和(sum部分）
select t.sname AS 姓名,
sum(decode(t.subject,'语文',grade,null))语文 ,
sum(decode(t.subject,'数学',grade,null)) 数学,
sum(decode(t.subject,'英语',grade,null)) 英语 
from Tb_Student t group by sname order by sname desc