原创

SQL 实现行列转换

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://david.blog.csdn.net/article/details/6258058

今天有个网友有这样的需求,那就看看这个实例吧

 

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
Insert tb
Select '张三','语文',60 union all
Select '张三','数学',70 union all
Select '张三','英语',80 union all
Select '张三','物理',90 union all
Select '李四','语文',65 union all
Select '李四','数学',75 union all
Select '李四','英语',85 union all
Select '李四','物理',95
go

第一种:
select [姓名]=max([姓名]), 数学=sum(case when [课程]='数学' then [分数] else 0 end),物理=sum(case when [课程]='物理' then [分数] else 0 end),英语=sum(case when [课程]='英语' then [分数] else 0 end),语文=sum(case when [课程]='语文' then [分数] else 0 end) from tb group by [姓名]
第二种:
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql+[课程]+'=sum(case when [课程]='''+[课程]+''' then [分数] else 0 end),' from (SELECT DISTINCT [课程] FROM TB) A
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select [姓名]=max([姓名]), '+@sql+' from tb group by [姓名] '
exec (@sql)

 


create table #TABLE (Area varchar(10), Date varchar(10), Count int)
go
insert into #TABLE (Area, Date, Count)
values ('BeiJing', '2007-01-01',100000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou', '2007-01-01',200000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-02-19',300000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-02-19',400000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-03-21',500000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-03-21',600000)
go
--sql2000
SELECT Area,
 '2007-01-01'=SUM(CASE Date WHEN '2007-01-01'  THEN [Count] END),
 '2007-02-19'=SUM(CASE Date WHEN '2007-02-19'  THEN [Count] END),
 '2007-03-21'=SUM(CASE Date WHEN '2007-03-21'  THEN [Count] END)
FROM #table
GROUP BY Area
go
--sql2005
SELECT *  FROM
#TABLE
PIVOT(SUM([Count]) FOR Date IN (
 [2007-01-01],[2007-02-19],[2007-03-21])) b

文章最后发布于: 2011-03-18 09:24:00
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 鲸 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览