prepare amount description itemtype
damao 1 平时加班 1
damao 2 平时加班 1
damao 3 法定节假日加班 1
damao 4 法定节假日加班 1
ermao 5 平时加班 1
ermao 6 平时加班 1
ermao 7 法定节假日加班 1
ermao 8 法定节假日加班 1
aaaaa 23 其它 2 prepare 平时加班 法定节假日加班
damao 3 7
ermao 11 15我想实现itemtye为1的数据 实现行转列
damao 1 平时加班 1
damao 2 平时加班 1
damao 3 法定节假日加班 1
damao 4 法定节假日加班 1
ermao 5 平时加班 1
ermao 6 平时加班 1
ermao 7 法定节假日加班 1
ermao 8 法定节假日加班 1
aaaaa 23 其它 2 prepare 平时加班 法定节假日加班
damao 3 7
ermao 11 15我想实现itemtye为1的数据 实现行转列
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) select 姓名 , [语文] , [数学] , [物理] from tb
pivot(max(分数) for 课程 in([语文] , [数学] , [物理])) pvt
sum(case description when '平时加班' then amount else 0 end) 平时加班,
sum(case description when '法定节假日加班' then amount else 0 end) 法定节假日加班
from tb where itemtype = 1
group by prepare
select prepare,
sum(case when description when '平时加班' then amont end) 平时加班,
sum(case when description when '法定节假日加班' then amont end )法定节假日加班,
sum(case when description when '其它' then amont end 其它)
group by prepare
insert into tb values('damao', 1 ,'平时加班', 1)
insert into tb values('damao', 2 ,'平时加班', 1)
insert into tb values('damao', 3 ,'法定节假日加班', 1)
insert into tb values('damao', 4 ,'法定节假日加班', 1)
insert into tb values('ermao', 5 ,'平时加班', 1)
insert into tb values('ermao', 6 ,'平时加班', 1)
insert into tb values('ermao', 7 ,'法定节假日加班', 1)
insert into tb values('ermao', 8 ,'法定节假日加班', 1)
insert into tb values('aaaaa', 23,'其它', 2 )
goselect prepare ,
sum(case description when '平时加班' then amount else 0 end) 平时加班,
sum(case description when '法定节假日加班' then amount else 0 end) 法定节假日加班
from tb where itemtype = 1
group by preparedrop table tb/*
prepare 平时加班 法定节假日加班
---------- ----------- -----------
damao 3 7
ermao 11 15(所影响的行数为 2 行)*/
if not object_id('tb') is null
drop table tb
go
create table tb(prepare varchar(10),amount int,description varchar(20),itemtype int)
insert into tb values('damao', 1 ,'平时加班', 1)
insert into tb values('damao', 2 ,'平时加班', 1)
insert into tb values('damao', 3 ,'法定节假日加班', 1)
insert into tb values('damao', 4 ,'法定节假日加班', 1)
insert into tb values('ermao', 5 ,'平时加班', 1)
insert into tb values('ermao', 6 ,'平时加班', 1)
insert into tb values('ermao', 7 ,'法定节假日加班', 1)
insert into tb values('ermao', 8 ,'法定节假日加班', 1)
insert into tb values('aaaaa', 23,'其它', 2 )
goselect prepare,
sum(case description when '平时加班' then amount else 0 end) '平时加班',
sum(case description when '法定节假日加班' then amount else 0 end) '法定节假日加班'
from tb
where itemtype = 1
group by prepareprepare 平时加班 法定节假日加班
---------- ----------- -----------
damao 3 7
ermao 11 15(2 行受影响)
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([prepare] nvarchar(5),[amount] int,[description] nvarchar(7),[itemtype] int)
Insert #
select N'damao',1,N'平时加班',1 union all
select N'damao',2,N'平时加班',1 union all
select N'damao',3,N'法定节假日加班',1 union all
select N'damao',4,N'法定节假日加班',1 union all
select N'ermao',5,N'平时加班',1 union all
select N'ermao',6,N'平时加班',1 union all
select N'ermao',7,N'法定节假日加班',1 union all
select N'ermao',8,N'法定节假日加班',1 union all
select N'aaaaa',23,N'其它',2
Go
DECLARE @s NVARCHAR(4000)
SET @s='select [prepare]'
Select @s=@s+','+QUOTENAME([description])+'=sum(case when [description]=N'''+[description]+''' then [amount] else 0 end)'
from #
WHERE [itemtype]=1
GROUP BY [description]EXEC(@s+' from # where [itemtype]=1 group by [prepare]')/*
prepare 平时加班 法定节假日加班
damao 3 7
ermao 11 15
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(prepare varchar(10),amount int,description varchar(20),itemtype int)
insert into tb values('damao', 1 ,'平时加班', 1)
insert into tb values('damao', 2 ,'平时加班', 1)
insert into tb values('damao', 3 ,'法定节假日加班', 1)
insert into tb values('damao', 4 ,'法定节假日加班', 1)
insert into tb values('ermao', 5 ,'平时加班', 1)
insert into tb values('ermao', 6 ,'平时加班', 1)
insert into tb values('ermao', 7 ,'法定节假日加班', 1)
insert into tb values('ermao', 8 ,'法定节假日加班', 1)
insert into tb values('aaaaa', 23,'其它', 2 )
go
declare @s varchar(max)
set @s=''
select @s=@s+','+QUOTENAME(description)+' = sum(case description when '''+description+''' then amount else 0 end)'
from tb
where itemtype=1
group by description
exec('select prepare'+@s+' from tb where itemtype=1 group by prepare')prepare 法定节假日加班 平时加班
damao 7 3
ermao 15 11
drop table tb
create table tb
(
prepare varchar(10),
amount int,
description varchar(20),
itemtype int
)
insert into tb values('damao', 1 ,'平时加班', 1)
insert into tb values('damao', 2 ,'平时加班', 1)
insert into tb values('damao', 3 ,'法定节假日加班', 1)
insert into tb values('damao', 4 ,'法定节假日加班', 1)
insert into tb values('ermao', 5 ,'平时加班', 1)
insert into tb values('ermao', 6 ,'平时加班', 1)
insert into tb values('ermao', 7 ,'法定节假日加班', 1)
insert into tb values('ermao', 8 ,'法定节假日加班', 1)
insert into tb values('aaaaa', 23,'其它', 2 )
select * from tbselect prepare,sum(case when description ='平时加班' then amount else 0 end) as '平时加班',
sum(case when description ='法定节假日加班' then amount else 0 end) as '法定节假日加班'
from tb
where itemtype = 1
group by prepare,itemtype--结果
damao 3 7
ermao 11 15
--测试
if object_id('tab') is not null drop table tbcreate table tb
(
name char(12),
amount int,
description char(20),
itemtype int
)insert into tb select 'daomao',1,'平时加班',1 union allselect 'daomao',2,'平时加班',1 union allselect 'daomao',3,'法定节假日加班',1 union allselect 'daomao',4,'法定节假日加班',1 union allselect 'ermao',5, '平时加班',1 union allselect 'ermao',6,'平时加班',1 union allselect 'ermao',7,'法定节假日加班',1 union allselect 'ermao',8,'法定节假日加班' ,1 union all select 'aaaaa',23,'其它',0select *from tbselect name,
sum(case description when '平时加班' then amount else 0 end ) as'平时加班',
sum (case description when '法定节假日加班' then amount else 0 end) as '法定节假日加班'
from tb
where itemtype=1
group by name解答过程中少了一个逗号,杯具
drop table tb
go
create table tb(prepare varchar(10),amount int,description varchar(20),itemtype int)
insert into tb values('damao', 1 ,'平时加班', 1)
insert into tb values('damao', 2 ,'平时加班', 1)
insert into tb values('damao', 3 ,'法定节假日加班', 1)
insert into tb values('damao', 4 ,'法定节假日加班', 1)
insert into tb values('ermao', 5 ,'平时加班', 1)
insert into tb values('ermao', 6 ,'平时加班', 1)
insert into tb values('ermao', 7 ,'法定节假日加班', 1)
insert into tb values('ermao', 8 ,'法定节假日加班', 1)
insert into tb values('aaaaa', 23,'其它', 2 )
goselect *from tb
drop table tb
SELECT prepare,平时加班,法定节假日加班
FROM tb
PIVOT(SUM(amount) FOR [description]
IN(让他,平时加班,法定节假日加班))AS P
结果为:
prepare 平时加班 法定节假日加班
---------- ----------- -----------
damao 3 7
ermao 11 15
aaaaa NULL NULL这是SQL 2005新增的函数,我也没有掌握好,无法将最后一行搞掉