--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (code varchar(3),name varchar(6),rq datetime,jb varchar(2))
insert into #T
select '001','刘汉丽','2008-4-1','优' union all
select '001','刘汉丽','2008-4-2','良' union all
select '001','刘汉丽','2008-4-3','差' union all
select '002','郜红艳','2008-4-1','优' union all
select '002','郜红艳','2008-4-2','良'declare @day tinyint, @max tinyint,@sql nvarchar(4000)select @day = min(day(rq)), @max = max(day(rq)) from #T
set @sql='select code, name, 年月=convert(varchar(6),rq,112)'while @day<=@max
begin
set @sql=@sql+',['+ltrim(@day)+'日]=max(case day(rq) when '+ltrim(@day)+' then jb else '''' end)'
set @day=@day+1
endset @sql=@sql+' from #T group by code,name,convert(varchar(6),rq,112)'exec (@sql)/*
code name 年月 1日 2日 3日
---- ------ ------ ---- ---- ----
001 刘汉丽 200804 优 良 差
002 郜红艳 200804 优 良
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (code varchar(3),name varchar(6),rq datetime,jb varchar(2))
insert into #T
select '001','刘汉丽','2008-4-1','优' union all
select '001','刘汉丽','2008-4-2','良' union all
select '001','刘汉丽','2008-4-3','差' union all
select '002','郜红艳','2008-4-1','优' union all
select '002','郜红艳','2008-4-2','良'declare @day tinyint, @max tinyint,@sql nvarchar(4000)select @day = min(day(rq)), @max = max(day(rq)) from #T
set @sql='select code, name, 年月=convert(varchar(6),rq,112)'while @day<=@max
begin
set @sql=@sql+',['+ltrim(@day)+'日]=max(case day(rq) when '+ltrim(@day)+' then jb else '''' end)'
set @day=@day+1
endset @sql=@sql+' from #T group by code,name,convert(varchar(6),rq,112)'exec (@sql)/*
code name 年月 1日 2日 3日
---- ------ ------ ---- ---- ----
001 刘汉丽 200804 优 良 差
002 郜红艳 200804 优 良
*/
insert into tb
select '001','刘汉丽','2008-4-1','优' union all
select '001','刘汉丽','2008-4-2','良' union all
select '001','刘汉丽','2008-4-3','差' union all
select '002','郜红艳','2008-4-1','优' union all
select '002','郜红艳','2008-4-2','良' declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+ltrim(datepart(dd,rq))+'日]=max(case when rq='''+ltrim(rq)+''' then jb else '''' end)' from tb group by rq
set @sql='select code,name'+@sql+' from tb group by code,name order by code'
exec (@sql)/*
code name 1日 2日 3日
--------------------------------------
001 刘汉丽 优 良 差
002 郜红艳 优 良
*/drop table tb
Limpire 正解
bqb 没有考虑到楼主不可能只有一个月
insert into ta
select '001','刘汉丽','2008-4-1','优' union all
select '001','刘汉丽','2008-4-2','良' union all
select '001','刘汉丽','2008-4-3','差' union all
select '002','郜红艳','2008-4-1','优' union all
select '002','郜红艳','2008-4-2','良'
godeclare @sql varchar(8000)
select @sql = isnull(@sql+',','') + '
['+convert(char(10),rq,120)+']= max(case when convert(char(10),rq,120) = '''+convert(char(10),rq,120)+''' then jb else null end) '
from
( select distinct rq from ta) aexec('select code ,name, convert(char(7),rq,120) as rq ,'+ @sql + ' from ta group by code,name,convert(char(7),rq,120)')drop table ta
/*
code name rq 2008-04-01 2008-04-02 2008-04-03
---- ------ ------- ---------- ---------- ----------
001 刘汉丽 2008-04 优 良 差
002 郜红艳 2008-04 优 良 NULL警告: 聚合或其它 SET 操作消除了空值。
*/