declare @t table
(
current_day date,
user_id int,
user_name nvarchar(30),
class_id int,
class_type int
)
insert into @t values ('2017-12-20',16,'销售1',3,2);
insert into @t values ('2017-12-20',17,'销售主管',1,2);
insert into @t values ('2017-12-20',15,'技术管理员',2,1);
insert into @t values ('2017-12-20',18,'文案1',1,0);
insert into @t values ('2017-12-20',15,'技术管理员',4,0);
insert into @t values ('2017-12-20',20,'人事1',4,2);
insert into @t values ('2017-12-21',16,'销售1',1,2);
insert into @t values ('2017-12-21',17,'销售主管',3,2);
insert into @t values ('2017-12-21',18,'文案1',1,0);
insert into @t values ('2017-12-21',15,'技术管理员',4,0);
insert into @t values ('2017-12-21',20,'人事1',4,0);
insert into @t values ('2017-12-22',16,'销售1',1,2);
insert into @t values ('2017-12-22',17,'销售主管',1,2);
insert into @t values ('2017-12-22',18,'文案1',1,0);
insert into @t values ('2017-12-22',15,'技术管理员',4,0);
insert into @t values ('2017-12-22',20,'人事1',4,0);
create table #t
(
current_day date,
user_id int,
user_name nvarchar(30),
class_id int,
class_type int
)
insert into #t values ('2017-12-20',16,'销售1',3,2);
insert into #t values ('2017-12-20',17,'销售主管',1,2);
insert into #t values ('2017-12-20',15,'技术管理员',2,1);
insert into #t values ('2017-12-20',18,'文案1',1,0);
insert into #t values ('2017-12-20',15,'技术管理员',4,0);
insert into #t values ('2017-12-20',20,'人事1',4,2);
insert into #t values ('2017-12-21',16,'销售1',1,2);
insert into #t values ('2017-12-21',17,'销售主管',3,2);
insert into #t values ('2017-12-21',18,'文案1',1,0);
insert into #t values ('2017-12-21',15,'技术管理员',4,0);
insert into #t values ('2017-12-21',20,'人事1',4,0);
insert into #t values ('2017-12-22',16,'销售1',1,2);
insert into #t values ('2017-12-22',17,'销售主管',1,2);
insert into #t values ('2017-12-22',18,'文案1',1,0);
insert into #t values ('2017-12-22',15,'技术管理员',4,0);
insert into #t values ('2017-12-22',20,'人事1',4,0);
declare @sql varchar(max)
select @sql=ISNULL(@sql+',','')+'max(case when current_day='''+cast(current_day as varchar)+''' then class_id else 0 end)'
from #t
group by current_day
set @sql=';with cte_1
as
(select *
from #t A
where not exists (select 1 from #t where user_id=A.user_id and current_day=A.current_day and class_type>A.class_type))
select user_name,'+@sql+' from cte_1 group by user_id,user_name'
exec(@sql)
declare @sql varchar(max)
select @sql=ISNULL(@sql+',','')+'max(case when current_day='''+cast(current_day as varchar)+''' then class_id else 0 end) as '''+cast(current_day as varchar)+''''
from #t
group by current_day
set @sql=';with cte_1
as
(select *
from #t A
where not exists (select 1 from #t where user_id=A.user_id and current_day=A.current_day and class_type>A.class_type))
select user_name,'+@sql+' from cte_1 group by user_id,user_name'
exec(@sql)
declare @cols varchar(max),@sql varchar(max)
select @cols=isnull(@cols+',','')+quotename(current_day) from #t group by current_day
set @sql='select * from (
select current_day,user_id,user_name,class_id from (
select *,row_number()over(partition by user_id,current_day order by class_type desc) as rn from #t
) as t where t.rn=1
) as t
pivot(max(class_id) for current_day in ('+@cols+')) p'
EXEC(@sql)
+---------+-----------+------------+------------+------------+
| user_id | user_name | 2017-12-20 | 2017-12-21 | 2017-12-22 |
+---------+-----------+------------+------------+------------+
| 20 | 人事1 | 4 | 4 | 4 |
| 15 | 技术管理员 | 2 | 4 | 4 |
| 18 | 文案1 | 1 | 1 | 1 |
| 16 | 销售1 | 3 | 1 | 1 |
| 17 | 销售主管 | 1 | 3 | 1 |
+---------+-----------+------------+------------+------------+
