22,209
社区成员
发帖
与我相关
我的任务
分享
--修改一下:
declare @sql varchar(max),@i int,@name char(100)
declare @tb table (id int identity(1,1),_name char(100))
insert into @tb select distinct 班别 from tb
select @i=1
while(@i<=(select count(*) from @tb))
begin
select top 1 @name=_name from @tb where id not in (select top (@i-1) id from @tb)
set @sql=isnull(@sql+','+@name+'=case 班别 when '''+@name+''' then temp end',@name+'=case 班别 when '''+@name+''' then temp end')
set @i=@i+1
end
set @sql='select 时间,'+@sql+' from (
select 时间,班别,
temp=stuff((select '',''+rtrim(员工) from tb where 时间=t.时间 for xml path('''')),1,1,'''') from tb t
group by 时间,班别 )t'
exec(@sql)
'这次不需要班别的具体信息了,你看下能实现不...'
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
时间 char(11),
员工 char(7),
班别 char(5)
)
go
--插入测试数据
insert into tb select '2010-04-23','王梅','早班'
union all select '2010-04-25','周晓丽','晚班'
union all select '2010-04-25','刘燕','晚班'
union all select '2010-04-25','王梅','晚班'
union all select '2010-04-23','明月','早班'
union all select '2010-04-24','陈红云','早班'
union all select '2010-04-24','孙红梅','早班'
union all select '2010-04-24','刘燕','早班'
union all select '2010-04-23','孙红梅','早班'
union all select '2010-04-23','陈红云','早班'
go
--代码实现
declare @sql varchar(1000),@i int,@name char(4)
declare @tb table (id int identity(1,1),_name char(4))
insert into @tb select distinct 班别 from tb
select @i=1
while(@i<=(select count(*) from @tb))
begin
select top 1 @name=_name from @tb where id not in (select top (@i-1) id from @tb)
set @sql=isnull(@sql+','+@name+'=case 班别 when '''+@name+''' then temp end',@name+'=case 班别 when '''+@name+''' then temp end')
set @i=@i+1
end
set @sql='select 时间,'+@sql+' from (
select 时间,班别,
temp=stuff((select '',''+rtrim(员工) from tb where 时间=t.时间 for xml path('''')),1,1,'''') from tb t
group by 时间,班别 )t'
exec(@sql)
--select 时间,早班=case 班别 when '早班' then temp end,
--中班=case 班别 when '中班' then temp end,
--晚班=case 班别 when '晚班' then temp end
--from (
--select 时间,班别,
--temp=stuff((select ','+rtrim(员工) from tb where 时间=t.时间 for xml path('')),1,1,'') from tb t
--group by 时间,班别 )t
/*测试结果
时间 晚班 中班
--------------------------------------------------------------
2010-04-23 NULL 王梅,明月,孙红梅,陈红云
2010-04-24 NULL 陈红云,孙红梅,刘燕
2010-04-25 周晓丽,刘燕,王梅 NULL
(3 行受影响)
*/
create table [tb]([时间] datetime,[员工] varchar(6),[班别] varchar(4))
insert [tb]
select '2010-04-23','王梅','早班' union all
select '2010-04-25','周晓丽','晚班' union all
select '2010-04-25','刘燕','晚班' union all
select '2010-04-25','王梅','晚班' union all
select '2010-04-23','明月','早班' union all
select '2010-04-24','陈红云','早班' union all
select '2010-04-24','孙红梅','早班' union all
select '2010-04-24','刘燕','早班' union all
select '2010-04-23','孙红梅','早班' union all
select '2010-04-23','陈红云','早班'
go
create function dbo.f_str(@时间 datetime , @班别 varchar(4)) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(员工 as varchar) from tb where 时间 = @时间 and 班别 = @班别
set @str = right(@str , len(@str) - 1)
return @str
end
go
--调用函数+行列转换
select convert(varchar(10),时间,120) 时间,
max(case 班别 when '早班' then 员工 else '' end) [早班],
max(case 班别 when '中班' then 员工 else '' end) [中班],
max(case 班别 when '晚班' then 员工 else '' end) [晚班]
from
(
select 时间 , 班别 , 员工 = dbo.f_str(时间 , 班别) from tb group by 时间 , 班别
) t
group by convert(varchar(10),时间,120)
drop function dbo.f_str
drop table tb
/*
时间 早班 中班 晚班
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
2010-04-23 王梅,明月,孙红梅,陈红云
2010-04-24 陈红云,孙红梅,刘燕
2010-04-25 周晓丽,刘燕,王梅
(所影响的行数为 3 行)
*/