declare @sql nvarchar(4000)
set @sql = 'select 员工编号'select @sql = @sql + ',sum (case convert(nvarchar(1),[月]) when '''+ convert(nvarchar(1),月) +''' then 奖金 else 0 end ) as [' + convert(nvarchar(1),月) + '月]'
from jj group by 月select @sql = @sql + 'from (select * from jj )b group by 员工编号'exec(@sql)
---'from (select * from jj )b
这是动态查出来的.
set @sql = 'select 员工编号'select @sql = @sql + ',sum (case convert(nvarchar(1),[月]) when '''+ convert(nvarchar(1),月) +''' then 奖金 else 0 end ) as [' + convert(nvarchar(1),月) + '月]'
from jj group by 月select @sql = @sql + 'from (select * from jj )b group by 员工编号'exec(@sql)
---'from (select * from jj )b
这是动态查出来的.
解决方案 »
- sql语句错行问题
- 这里谁用过SQL SERVER的编程接口函数?如何取得一个存储过程的出口参数值?
- 如何在SQLSERVER中使用SQL语句在ACCESS MDB中创建表?
- sqlserver 读取 dbf文件
- A 表和B表连接成一个记录A 和 B 的共同标识 TaskID,但有可能A里没有B中的TaskID cross, LEFT OUTER JOIN不能达到要求
- 寻求SQL语句的写法
- 这条数据过滤的SQL语句如何写呢?
- 向大力请教关于定时数据同步
- 请问,如何取得记录集?
- SqlServer 自定义函数里面不能执行getdate函数?
- 报表一张
- 难题,如何判断同一行数据中的任意列值是否相等?
select a.emp_id,sum(a.holiday_time) as holiday_time_sum,a.holiday_type
from holiday_types a
left join holiday_employees b on a.holiday_type= b.holiday_type
group by a.emp_id,a.holiday_type查询处来得结果为:
emp_id holiday_time_sum holiday_type
01 20.01 A
01 10.02 B
01 11.03 C
02 12 A
02 10 C我想的到的结果为:
emp_id A B C
01 20.01 10.02 11.03
02 12 10
holiday_type holiday_type_name work_hour
------------ -------------------- ---------------------------------------
A 婚假 8.00
B 产假 8.00
C 丧假 8.00
D 病假 8.00
E tst 5.00table:holiday_employees
holiday_emp_seq emp_id start_day end_day holiday_type
--------------- ---------- ----------------------- ----------------------- ------------
1 01 2007-08-01 00:00:00.000 2007-08-02 00:00:00.000 A
2 01 2007-08-07 00:00:00.000 2007-08-09 00:00:00.000 B
3 01 2007-08-12 00:00:00.000 2007-08-20 00:00:00.000 C
4 02 2007-08-12 00:00:00.000 2007-08-20 00:00:00.000 C
5 03 2007-08-12 00:00:00.000 2007-08-20 00:00:00.000 C
6 03 2007-08-01 00:00:00.000 2007-08-03 00:00:00.000 A
我想的到的结果为:
emp_id A B C
01 20.01 10.02 11.03
02 12 10
其中 假期的计算是:work_hour*datadiff(day,start_day,end_day)
Declare @S Varchar(8000)
Select @S = ' Select A.emp_id'
Select @S = @S + ', SUM(Case A.holiday_type When ''' + holiday_type + ''' Then SUM(B.work_hour * datadiff(day, start_day, end_day)) Else 0.0 End) As [' + holiday_type + ']'
From holiday_employees Group By holiday_type
Select @S = @S +' From holiday_employees A Inner Join holiday_type B On A.holiday_type = B.holiday_type Group By A.emp_id'
EXEC(@S)
Declare @S Varchar(8000)
Select @S = ' Select A.emp_id'
Select @S = @S + ', SUM(Case A.holiday_type When ''' + holiday_type + ''' Then B.work_hour * datediff(day, start_day, end_day) Else 0.0 End) As [' + holiday_type + ']'
From holiday_employees Group By holiday_type
Select @S = @S +' From holiday_employees A Inner Join holiday_type B On A.holiday_type = B.holiday_type Group By A.emp_id'
EXEC(@S)
PS:
樓主的datediff寫錯了
create table holiday_type
(holiday_type Varchar(10),
holiday_type_name Nvarchar(20),
work_hour Numeric(10, 2))
Insert holiday_type Select 'A', N'婚假', 8.00
Union All Select 'B', N'产假', 8.00
Union All Select 'C', N'丧假', 8.00
Union All Select 'D', N'病假', 8.00
Union All Select 'E', N'tst', 5.00create table holiday_employees
(holiday_emp_seq Int,
emp_id Char(2),
start_day DateTime,
end_day DateTime,
holiday_type Varchar(10))
Insert holiday_employees Select 1, '01', '2007-08-01 00:00:00.000', '2007-08-02 00:00:00.000', 'A'
Union All Select 2, '01', '2007-08-07 00:00:00.000', '2007-08-09 00:00:00.000', 'B'
Union All Select 3, '01', '2007-08-12 00:00:00.000', '2007-08-20 00:00:00.000', 'C'
Union All Select 4, '02', '2007-08-12 00:00:00.000', '2007-08-20 00:00:00.000', 'C'
Union All Select 5, '03', '2007-08-12 00:00:00.000', '2007-08-20 00:00:00.000', 'C'
Union All Select 6, '03', '2007-08-01 00:00:00.000', '2007-08-03 00:00:00.000', 'A'
GO
--測試
Declare @S Varchar(8000)
Select @S = ' Select A.emp_id'
Select @S = @S + ', SUM(Case A.holiday_type When ''' + holiday_type + ''' Then B.work_hour * datediff(day, start_day, end_day) Else 0.0 End) As [' + holiday_type + ']'
From holiday_employees Group By holiday_type
Select @S = @S +' From holiday_employees A Inner Join holiday_type B On A.holiday_type = B.holiday_type Group By A.emp_id'
EXEC(@S)
--刪除測試環境
Drop Table holiday_employees, holiday_type
--結果
/*
emp_id A B C
01 8.00 16.00 64.00
02 .00 .00 64.00
03 16.00 .00 64.00
*/