如何得到sql来获得结果？ [英] how to wirte the sql to get the result?

问题描述

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]

推荐答案

你可以制作一个支点选择。



试试这个：

You can make a pivot Select.

Try this:

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来获得结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！