if OBJECT_ID('t') is not null drop table t; gocreate table t (部门 int,姓名 varchar(10),日期 date,记录 varchar(10)); insert into t select 2 ,'张三', '2011-1-1', '正常上班' union all select 3 ,'李四', '2011-1-1', '正常上班' union all select 2 ,'张三', '2011-1-2', '迟到 ' union all select 3 ,'李四', '2011-1-2', '正常上班' union all select 2 ,'张三', '2011-1-3', '正常上班' union all select 3 ,'李四', '2011-1-3', '请假一天' ;select * from t;declare @fi nvarchar(500) declare @in nvarchar(500) declare @sql nvarchar(max) select @fi=(select ', ['+cast(日期 as varchar)+'] as ['+cast(日期 as varchar)+']' from t group by 日期 for xml path('')),@in=(select ', ['+cast(日期 as varchar)+']' from t group by cast(日期 as varchar) for xml path(''));select @sql=' SELECT 部门,姓名'+@fi+' FROM t PIVOT (MAX(记录) FOR 日期 in ('+STUFF(@in,1,1,'')+') ) pvt'; exec(@sql);/* 部门 姓名 2011-01-01 2011-01-02 2011-01-03 ----------- ---------- ---------- ---------- ---------- 3 李四 正常上班 正常上班 请假一天 2 张三 正常上班 迟到 正常上班 */
把lz的输入稍微改了一下 输入的汉字“一”改为“1”,“一天半”改为“1.5天” use tempdb; /* create table 考勤表 ( 部门 int not null, 姓名 nvarchar(10) not null, 日期 date not null, 记录 nvarchar(10) not null ); insert into 考勤表(部门,姓名,日期,记录) values (2,'张三','2011-1-1','正常上班'), (3,'李四','2011-1-1','正常上班'), (2,'张三','2011-1-2','迟到'), (3,'李四','2011-1-2','正常上班'), (2,'张三','2011-1-3','正常上班'), (3,'李四','2011-1-3','请假1天'), (3,'李四','2011-1-5','请假0.5天'); */ declare @sql varchar(8000) set @sql = 'select 部门,姓名, SUM(case when 记录 = ''迟到'' then 1 else 0 end) as [迟到合计],SUM(case when 记录 like ''请假%'' then CAST(REPLACE(REPLACE(记录,''请假'',''''),''天'','''') AS decimal(18,1)) else 0 end) as [请假合计]' --SUM(case when 记录 like '请假%' then CAST(REPLACE(REPLACE(记录,'请假',''),'天','') AS decimal(18,2)) else 0 end) as [请假合计]' select @sql = @sql + ' , MAX(case 日期 when ''' + 日期 + ''' then 记录 end) as [' + 日期 + ']' from (select distinct CAST(日期 as nvarchar) as 日期 from 考勤表) as a set @sql = @sql + ' from 考勤表 group by 部门,姓名' exec(@sql)
把lz的输入稍微改了一下 输入的汉字“一”改为“1”,“一天半”改为“1.5天” use tempdb; /* create table 考勤表 ( 部门 int not null, 姓名 nvarchar(10) not null, 日期 date not null, 记录 nvarchar(10) not null ); insert into 考勤表(部门,姓名,日期,记录) values (2,'张三','2011-1-1','正常上班'), (3,'李四','2011-1-1','正常上班'), (2,'张三','2011-1-2','迟到'), (3,'李四','2011-1-2','正常上班'), (2,'张三','2011-1-3','正常上班'), (3,'李四','2011-1-3','请假1天'), (3,'李四','2011-1-5','请假0.5天'); */ declare @sql varchar(8000) set @sql = 'select 部门,姓名' +', SUM(case when 记录 = ''迟到'' then 1 else 0 end) as [迟到合计]' +',SUM(case when 记录 like ''请假%'' then CAST(REPLACE(REPLACE(记录,''请假'',''''),''天'','''') AS decimal(18,1)) else 0 end) as [请假合计]' select @sql = @sql + ' , MAX(case 日期 when ''' + 日期 + ''' then 记录 end) as [' + 日期 + ']' from (select distinct CAST(日期 as nvarchar) as 日期 from 考勤表) as a set @sql = @sql + ' from 考勤表 group by 部门,姓名' exec(@sql)
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。问题:假设有张学生成绩表(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) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------
现在好像还是不行,要实现这种 本月迟到 本月请假 2011-1-1 2011-1-2 2011-1-3
张三 1 正常上班 迟到 正常上班
李四 1.5 正常上班 正常上班 请假一天
drop table t;
gocreate table t (部门 int,姓名 varchar(10),日期 date,记录 varchar(10));
insert into t
select 2 ,'张三', '2011-1-1', '正常上班' union all
select 3 ,'李四', '2011-1-1', '正常上班' union all
select 2 ,'张三', '2011-1-2', '迟到 ' union all
select 3 ,'李四', '2011-1-2', '正常上班' union all
select 2 ,'张三', '2011-1-3', '正常上班' union all
select 3 ,'李四', '2011-1-3', '请假一天' ;select * from t;declare @fi nvarchar(500)
declare @in nvarchar(500)
declare @sql nvarchar(max)
select @fi=(select ', ['+cast(日期 as varchar)+'] as ['+cast(日期 as varchar)+']' from t group by 日期 for xml path('')),@in=(select ', ['+cast(日期 as varchar)+']' from t group by cast(日期 as varchar) for xml path(''));select @sql=' SELECT 部门,姓名'+@fi+' FROM t PIVOT (MAX(记录) FOR 日期 in ('+STUFF(@in,1,1,'')+') ) pvt';
exec(@sql);/*
部门 姓名 2011-01-01 2011-01-02 2011-01-03
----------- ---------- ---------- ---------- ----------
3 李四 正常上班 正常上班 请假一天
2 张三 正常上班 迟到 正常上班
*/
use tempdb;
/*
create table 考勤表
(
部门 int not null,
姓名 nvarchar(10) not null,
日期 date not null,
记录 nvarchar(10) not null
);
insert into 考勤表(部门,姓名,日期,记录)
values
(2,'张三','2011-1-1','正常上班'),
(3,'李四','2011-1-1','正常上班'),
(2,'张三','2011-1-2','迟到'),
(3,'李四','2011-1-2','正常上班'),
(2,'张三','2011-1-3','正常上班'),
(3,'李四','2011-1-3','请假1天'),
(3,'李四','2011-1-5','请假0.5天');
*/
declare @sql varchar(8000)
set @sql = 'select 部门,姓名, SUM(case when 记录 = ''迟到'' then 1 else 0 end) as [迟到合计],SUM(case when 记录 like ''请假%'' then CAST(REPLACE(REPLACE(记录,''请假'',''''),''天'','''') AS decimal(18,1)) else 0 end) as [请假合计]'
--SUM(case when 记录 like '请假%' then CAST(REPLACE(REPLACE(记录,'请假',''),'天','') AS decimal(18,2)) else 0 end) as [请假合计]'
select @sql = @sql + ' , MAX(case 日期 when ''' + 日期 + ''' then 记录 end) as [' + 日期 + ']'
from (select distinct CAST(日期 as nvarchar) as 日期 from 考勤表) as a
set @sql = @sql + ' from 考勤表 group by 部门,姓名'
exec(@sql)
use tempdb;
/*
create table 考勤表
(
部门 int not null,
姓名 nvarchar(10) not null,
日期 date not null,
记录 nvarchar(10) not null
);
insert into 考勤表(部门,姓名,日期,记录)
values
(2,'张三','2011-1-1','正常上班'),
(3,'李四','2011-1-1','正常上班'),
(2,'张三','2011-1-2','迟到'),
(3,'李四','2011-1-2','正常上班'),
(2,'张三','2011-1-3','正常上班'),
(3,'李四','2011-1-3','请假1天'),
(3,'李四','2011-1-5','请假0.5天');
*/
declare @sql varchar(8000)
set @sql = 'select 部门,姓名'
+', SUM(case when 记录 = ''迟到'' then 1 else 0 end) as [迟到合计]'
+',SUM(case when 记录 like ''请假%'' then CAST(REPLACE(REPLACE(记录,''请假'',''''),''天'','''') AS decimal(18,1)) else 0 end) as [请假合计]'
select @sql = @sql + ' , MAX(case 日期 when ''' + 日期 + ''' then 记录 end) as [' + 日期 + ']'
from (select distinct CAST(日期 as nvarchar) as 日期 from 考勤表) as a
set @sql = @sql + ' from 考勤表 group by 部门,姓名'
exec(@sql)