sql 行转列

[Caoxp_papa]

测试数据：

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);

实现结果：

如果一个人同一天有多条数据，获取class_type最大的那条记录的class_id显示

[RINK_1]

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)

[RINK_1]

前面一个忘记生成列名了，以这个为准。

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          |
+---------+-----------+------------+------------+------------+

[wwfxgm]

引用 3 楼 ch21st 的回复:

另外一种写法：

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          |
+---------+-----------+------------+------------+------------+

我没有看错时间吧？还是这个系统显示的时间是不对的？？？？

[道素]

引用 4 楼 wwfxgm 的回复:

[quote=引用 3 楼 ch21st 的回复:] 另外一种写法：

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          |
+---------+-----------+------------+------------+------------+

我没有看错时间吧？还是这个系统显示的时间是不对的？？？？[/quote] 我不在国内，所以和你们作息时间不同