27,581
社区成员




工号 姓名 check 年月 总工时 欠休天
10 aaa N 2016-03 153 -6
10 aaa N 2016-02 54 -18
13 bb N 2016-03 210.5 -2
13 bb N 2016-02 161.5 -6
10026 c N 2016-03 208.5 -1
15 dd N 2016-02 180.5 -1
15 dd N 2016-03 137 -3
工号 姓名 check 2月 总工时 欠休天 年月3 总工时 欠休天
10 aa N 2016-02 54 -18 2016-03 153 -6
13 bb n 2016-02 161.5 -6 2016-03 210.5 -2
10026 c n 2016-02 208.5 -1 2016-03
15 dd n 2016-02 180.5 -1 2016-03 137 -3
create table T(工号 varchar(10) ,姓名 varchar(10), [check] varchar(10), 年月 varchar(10), 总工时 float, 欠休天 int )
insert into T select '10','aaa','N','2016-03',153 ,-6
insert into T select '10','aaa','N','2016-02',14 ,-18
insert into T select '13','bb','N','2016-03',210.5,-2
insert into T select '13','bb','N','2016-02',261.5 ,-6
insert into T select '10026','C','N','2016-03',208.5 ,-1
insert into T select '15','dd','N','2016-02',137 ,-1
insert into T select '15','dd','N','2016-03',137 ,-3
declare @sql varchar(max)
select @sql=' select distinct 工号,姓名,[check]'
select @sql=@sql+','''+年月+''' as '''+name+''',(select 总工时 from T where 工号=a.工号 and 年月='''+年月+''') as 总工时'
+',(select 欠休天 from T where 工号=a.工号 and 年月='''+年月+''') as 欠休天'
from
(select distinct 年月,RIGHT(年月,1)+'月' as name from T) X
select @sql=@sql+' from T a'
--print @sql
exec @sql
/*
工号 姓名 check 2月 总工时 欠休天 3月 总工时 欠休天
---------------------------------------------------------------
10 aaa N 2016-02 14 -18 2016-03 153 -6
10026 C N 2016-02 NULL NULL 2016-03 208.5 -1
13 bb N 2016-02 261.5 -6 2016-03 210.5 -2
15 dd N 2016-02 137 -1 2016-03 137 -3
*/
drop table T
create table py
(工号 int, 姓名 varchar(10), [check] varchar(5), 年月 varchar(10), 总工时 decimal(8,1), 欠休天 int)
insert into py
select 10, 'aaa', 'N' ,'2016-03', 153, -6 union all
select 10, 'aaa', 'N', '2016-02', 54, -18 union all
select 13, 'bb', 'N', '2016-03', 210.5, -2 union all
select 13, 'bb', 'N', '2016-02', 161.5, -6 union all
select 10026, 'c', 'N', '2016-03', 208.5, -1 union all
select 15, 'dd', 'N', '2016-02', 180.5, -1 union all
select 15, 'dd', 'N', '2016-03', 137, -3
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+''''+年月+''' '''+right(年月,2)+'月'', '
+'max(case when 年月='''+年月+''' then 总工时 else 0 end) ''总工时'', '
+'max(case when 年月='''+年月+''' then 欠休天 else -10000 end) ''欠休天'' '
from (select distinct 年月 from py) t
select @tsql='select 工号,姓名,[check], '+@tsql
+' from py '
+' group by 工号,姓名,[check] '
exec(@tsql)
/*
工号 姓名 check 02月 总工时 欠休天 03月 总工时 欠休天
----------- ---------- ----- ------- --------------------------------------- ----------- ------- --------------------------------------- -----------
10 aaa N 2016-02 54.0 -18 2016-03 153.0 -6
13 bb N 2016-02 161.5 -6 2016-03 210.5 -2
15 dd N 2016-02 180.5 -1 2016-03 137.0 -3
10026 c N 2016-02 0.0 -10000 2016-03 208.5 -1
(4 row(s) affected)
*/
declare @sql varchar(100)
;with tb(工号 ,姓名,[check],年月,总工时,欠休天) as (
select 10,'aaa','N','2016-03',153,-6 union all
select 10,'aaa','N','2016-02',54,-18 union all
select 13,'bb','N','2016-03',210.5 ,-2 union all
select 13,'bb','N','2016-02',161.5,-6 union all
select 10026,'c','N','2016-03',208.5 , -1 union all
select 15,'dd','N','2016-02',180.5 , -1 union all
select 15,'dd','N','2016-03',137 , -3
)
select 工号 ,姓名,[check],[2016-02_总工时], [2016-02_欠休天], [2016-03_总工时], [2016-03_欠休天]
from (
select 工号 ,姓名,[check],年月+'_'+col as col,datavalue from tb as a
cross apply(
select N'总工时',a.总工时 union all
select N'欠休天',a.欠休天
) c(col,datavalue)
) d
pivot (
max(datavalue)
for col in ([2016-02_总工时], [2016-02_欠休天], [2016-03_总工时], [2016-03_欠休天])
) piv;
/*
工号 姓名 check 2016-02_总工时 2016-02_欠休天 2016-03_总工时 2016-03_欠休天
10 aaa N 54.0 -18.0 153.0 -6.0
13 bb N 161.5 -6.0 210.5 -2.0
15 dd N 180.5 -1.0 137.0 -3.0
10026 c N NULL NULL 208.5 -1.0
*/