22,199
社区成员
发帖
与我相关
我的任务
分享
select t1.姓名, t1.[2008-08-01] as N'2008-08-01上班', t2.[2008-08-01] as N'2008-08-01下班',
t1.[2008-08-02] as N'2008-08-02上班' , t2.[2008-08-02] as N'2008-08-02下班'
from
(
SELECT 姓名, [2008-08-01] , [2008-08-02]
from ( select 日期, 上班时间, 姓名 from ta) ta1
pivot
(max(上班时间) for 日期 in ([2008-08-01], [2008-08-02])) pivottable
) t1
join
(
SELECT 姓名, [2008-08-01], [2008-08-02]
from
( select 日期, 下班时间, 姓名 from ta) ta1
pivot
(max(下班时间) for 日期 in ([2008-08-01], [2008-08-02])) pivottable
) t2
on t1.姓名 = t2.姓名
select 姓名 ,
min(case 日期 when '2008-08-01' then convert(char(8),上班时间,114) end) [2008-08-01上班时间] ,
max(case 日期 when '2008-08-01' then convert(char(8),下班时间,114) end) [2008-08-01下班时间] ,
min(case 日期 when '2008-08-02' then convert(char(8),上班时间,114) end) [2008-08-02上班时间] ,
max(case 日期 when '2008-08-02' then convert(char(8),下班时间,114) end) [2008-08-02下班时间]
from elin0001 group by 姓名
create table elin0001( 日期 dateTime, 上班时间 dateTime, 下班时间 dateTime, 姓名 varchar(100))
insert into elin0001
select '2008-8-1' , '8:52' , '18:20 ' , '张三' union all
select '2008-8-1' , '9:00' , '19:01' , '王五' union all
select '2008-8-1' , '8:40' , '18:33' , '李四 ' union all
select '2008-8-1' , '8:40' , '18:33' , '赵一' union all
select '2008-8-2' , '8:52' , '19:20' , '张三' union all
select '2008-8-2' , '9:00' , '20:44' , '王五' union all
select '2008-8-2' , '8:40' , '17:55' , '李四' union all
select '2008-8-2 ' , '8:40' , '19:11' , '赵一'
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , min(case 日期 when ''' + convert(varchar(10),日期, 120) + ''' then convert(char(8),上班时间,114) end) [' + convert(varchar(10),日期, 120) + '上班时间]' from (select distinct 日期 from elin0001 ) a
select @sql = @sql + ' , max(case 日期 when ''' + convert(varchar(10),日期 ,120) + ''' then convert(char(8),下班时间,114) end) [' + convert(varchar(10),日期, 120) + '下班时间]' from (select distinct 日期 from elin0001 ) a
set @sql = @sql + ' from elin0001 group by 姓名'
exec(@sql)
姓名 2008-08-01上班 2008-08-02上班 2008-08-01下班 2008-08-02下班
李四 08:40:00 08:40:00 18:33:00 17:55:00
王五 09:00:00 09:00:00 19:01:00 20:44:00
张三 08:52:00 08:52:00 18:20:00 19:20:00
赵一 08:40:00 08:40:00 18:33:00 19:11:00
declare @tb table (日期 datetime,上班时间 nvarchar(10),下班时间 nvarchar(10),姓名 nvarchar(10))
insert into @tb select '2008-8-1','8:52','18:20','张三'
union all select '2008-8-1','9:00','19:01','王五'
union all select '2008-8-1','8:40','18:33','李四'
union all select '2008-8-1','8:40','18:33','赵一'
union all select '2008-8-2','8:52','19:20','张三'
union all select '2008-8-2','9:00','20:44','王五'
union all select '2008-8-2','8:40','17:55','李四'
union all select '2008-8-2','8:40','19:11','赵一'
--select * from (select 日期,上班时间,姓名 from @tb) a pivot (max(上班时间) for 日期 in ([2008-8-1],[2008-8-2])) b
select 姓名,[2008-8-1上班时间]=max(case when 日期='2008-8-1' then 上班时间 else '' end),
[2008-8-1下班时间]=max(case when 日期='2008-8-1' then 下班时间 else '' end),
[2008-8-2上班时间]=max(case when 日期='2008-8-2' then 上班时间 else '' end),
[2008-8-2上班时间]=max(case when 日期='2008-8-2' then 下班时间 else '' end )from @tb
group by 姓名
(8 行受影响)
姓名 2008-8-1上班时间 2008-8-1下班时间 2008-8-2上班时间 2008-8-2上班时间
---------- ------------ ------------ ------------ ------------
李四 8:40 18:33 8:40 17:55
王五 9:00 19:01 9:00 20:44
张三 8:52 18:20 8:52 19:20
赵一 8:40 18:33 8:40 19:11
(4 行受影响)
------------------------------------
-- Author:happyflystone
-- Version:V1.001
-- Date:2009-07-01 14:30:49
------------------------------------
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(日期 datetime,上班时间 nvarchar(6),下班时间 nvarchar(6),姓名 nvarchar(10))
Go
Insert into ta
select '2008-8-1','8:52','18:20','张三' union all
select '2008-8-1','9:00','19:01','王五' union all
select '2008-8-1','8:40','18:33','李四' union all
select '2008-8-1','8:40','18:33','赵一' union all
select '2008-8-2','8:52','19:20','张三' union all
select '2008-8-2','9:00','20:44','王五' union all
select '2008-8-2','8:40','17:55','李四' union all
select '2008-8-2','8:40','17:11','赵一'
Go
--Start
declare @s varchar(1000)
select @s = isnull(@s+',','')+'['+convert(char(10),日期,120)+'上班]= max(case when datediff(d,日期,'''+convert(char(10),日期,120)+''') = 0 then 上班时间 else '''' end),'
+'['+convert(char(10),日期,120)+'下班]= max(case when datediff(d,日期,'''+convert(char(10),日期,120)+''') = 0 then 下班时间 else '''' end)'
from (select distinct 日期 from ta) a
exec('select 姓名,'+@s+' from ta group by 姓名')
--Result:
/*
姓名 2008-08-01上班 2008-08-01下班 2008-08-02上班 2008-08-02下班
---------- ------------ ------------ ------------ ------------
李四 8:40 18:33 8:40 17:55
王五 9:00 19:01 9:00 20:44
张三 8:52 18:20 8:52 19:20
赵一 8:40 18:33 8:40 17:11
*/
--End
*
标题:普通行列转换(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')
---------------------------------