本文介绍了如何得到sql来获得结果？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
i有一个sql表
i have a table for sql
alter PROCEDURE [dbo].[SpGetEmpAttendance_test]
@DateFrom datetime
AS
BEGIN
select t5.SempAtndSempMstrId,SgShiftMaster.SshftShiftId,t5.SdeptDepartmentName,t5.available_strength from (
select t3.SempAtndSempMstrId,t3.SempAtndSempMstrSshftId,t4.SdeptDepartmentName,t3.available_strength from (
select t1.SempAtndSempMstrId,t1.SempAtndSempMstrSshftId,t2.SempMstrSdeptId,t2.available_strength from (SELECT SempAtndSempMstrId,SempAtndSempMstrSshftId,SempMstrSdeptId
from SgEmployeeAttendance inner join SgEmployeeMaster on SempAtndSempMstrId=SempMstrId and SempAtndSempMstrSshftId =SempMstrSshftId
where (SempAtndSempMstrSshftId=15 or SempAtndSempMstrSshftId=12 or SempAtndSempMstrSshftId=13 or SempAtndSempMstrSshftId=14 or SempAtndSempMstrSshftId=16)
and cast(convert(varchar(10),SempAtndTimeIn,101)as datetime) =''+cast(convert(varchar(10),@DateFrom,101) as varchar)+''
)as t1
inner join (
select SempMstrSdeptId, COUNT(*)as available_strength from SgEmployeeMaster
where (SempMstrSshftId=15 or SempMstrSshftId=12 or SempMstrSshftId=13 or SempMstrSshftId=14 or SempMstrSshftId=16)
group by SempMstrSdeptId)as t2 on t1.SempMstrSdeptId =t2.SempMstrSdeptId
) as t3
inner join (select * from SgDepartment)as t4 on t3.SempMstrSdeptId = t4.SdeptId)as t5
inner join SgShiftMaster on t5.SempAtndSempMstrSshftId =SgShiftMaster.SshftId
order by t5.SdeptDepartmentName,t5.SempAtndSempMstrSshftId
END
如下
as below
SempAtndSempMstrId SshftShiftId SdeptDepartmentName available_strength
3 A Attenders 6
16 A Attenders 6
17 B Attenders 6
21 B Attenders 6
15 C Attenders 6
22 G1 Doctors 4
18 G2 Doctors 4
20 G1 Pharmasy 2
14 G1 Pharmasy 2
然后我想得到如下结果。
then i want a result as below.
deptment strength A B C G1 G2
------- ------- --- -- -- -- --
Attenders 6 2 2 1 0 0
Doctors 4 0 0 0 1 1
Pharmasy 2 0 0 0 2 0
怎么写sql到得到以上结果。
谢谢。
代码块添加 - OriginalGriff [/ edit]
how to write a sql to get above result.
Thanks.
[edit]Code block added - OriginalGriff[/edit]
推荐答案
SELECT SdeptDepartmentName, available_strength, A, B, C, G1, G2
FROM
(SELECT SempAtndSempMstrId, SshftShiftId, SdeptDepartmentName, available_strength
FROM tmpTable) AS p
PIVOT
(
COUNT (SempAtndSempMstrId)
FOR SshftShiftId IN
( [A], [B], [C], [G1], [G2] )
) AS pvt
tmpTable是一个临时表，用于存储查询结果。
tmpTable is a temporary table where You store the result from Your query.
SELECT SdeptDepartmentName AS deptname, available_strength AS strength,
[A],[B],[C],[G1],[G2]
FROM
(SELECT SdeptDepartmentName, available_strength, SshftShiftId FROM tablename) AS sourcetable
PIVOT
(
COUNT(SshftShiftId) FOR SshftShiftId IN ([A],[B],[C],[G1],[G2])
)AS somename ORDER BY SdeptDepartmentName ASC
这篇关于如何得到sql来获得结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！