如何根据pivot中的条件对列进行计数 [英] How to count column based on condition in pivot

问题描述

我需要创建一个数据透视表，其中第一列包含员工姓名，下一列标题包含一个月的所有日期，最后一列将显示当前的天数



你可以帮我吗

  ALTER   procedure  [dbo]。[EmpAttandance]  @ iMon   int ， @ iyear   int  
  as  
 开始 
   -   将所有不同日期纳入临时表#Dates  
  SELECT   DISTINCT  ADate  INTO  #Dates 
  FROM  AttandanceTran 其中 DAT EPART（MM，ADate）= @ iMon 和 DATEPART（YYYY，ADate）= @ iyear 
  ORDER   BY  ADate 
 
 
   -   天数将是动态的。所以构建 
   -   来自#Dates  
  DECLARE   @cols   NVARCHAR （ 4000 ）
  SELECT   @cols  =  COALESCE （ @cols  + ' ，[' +  CONVERT （ varchar ，Adate， 106 ）
 + '  ]'，'  [' +  CONVERT （ varchar ，Adate， 106 ）+ ' ]'）
  FROM  #Dates 
  ORDER   BY  Adate 
 
   -   使用动态日期构建查询 
  DECLARE   @ qry   NVARCHAR （ 4000 ）
 < span class =code-keyword> SET   @ qry  = 
 '  SELECT * FROM 
（SELECT Empcode，Name，Type，Adate，count（type）over（PARTITION BY empcode）AS Tot 
 FROM AttandanceTran，Empmast where Empmast。 ECode = AttandanceTran.Ecode 
和DATEPART（MM，ADate）=' + CAST（ @ imon   as   varchar （ 10 ））+ '  
和DATEPART（YYYY，ADate）=' + CAST（ @ iyear   as   varchar （ 10 ））+ ' ）emp 
 PIVOT（最大（类型）FOR ADate IN（' +  @cols  + ' ））AS类型' 
 
 
   -   执行查询 
  EXEC （ @ qry  ）
 
 
   -   删除临时表 
  DROP   TABLE  #Dates 

end

解决方案

主要说明是：使用 JOIN 语句 WHERE 子句替换关系/>
替换：

  SELECT  Empcode，Name，类型，Adate，count（类型） over （ PARTITION   BY  empcode） AS  Tot 
  FROM  AttandanceTran，Empmast 其中 Empmast.ECode = AttandanceTran.Ecode 
 和 DATEPART（MM，ADate）= '  + CAST（@imon as varchar（10））+ ' 
 和 DATEPART（YYYY，ADate）= '  + CAST（@ iyear as varchar（10）） 

with：

  SELECT  em.Empcode，em.Name，em。 Type ，at.Adate，COUNT（at。 type ） AS  Tot 
  FROM  AttandanceTran  AS  at  INNER   JOIN  Empmast  AS  em  ON  at.ECode = em.ECode 
  WHERE  DATEPART（MM，ADate）= @ iMon  AND  DATEPART（YYYY，ADate）= @ iyear 

你看到了区别吗？我正在使用加入 [ ^ ]声明之间建立关系两张桌子。看看这里： SQL连接的可视化表示 [ ^ ]



如果您想获得列和/或行的总计，请参阅：

SQL - 带有总列和行的枢轴 [ ^ ]

在SQL查询中使用Pivot的简单方法 [ ^ ]

I Need to create a pivot table in which first column contain the employee name and next column header contain the all the date of a month and last column will show how many present days

can you help me

ALTER procedure [dbo].[EmpAttandance] @iMon int,@iyear int
as
begin
-- Getting all distinct dates into a temporary table #Dates
SELECT DISTINCT ADate INTO #Dates
FROM AttandanceTran where DATEPART(MM,ADate) =@iMon and DATEPART(YYYY,ADate)=@iyear
ORDER BY ADate


-- The number of days will be dynamic. So building
-- a comma seperated value string from the dates in #Dates
DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, Adate, 106)
				+ ']','[' + CONVERT(varchar, Adate, 106) + ']')
FROM    #Dates
ORDER BY Adate

-- Building the query with dynamic dates
DECLARE @qry NVARCHAR(4000)
SET @qry =
'SELECT * FROM
(SELECT Empcode,Name,Type,Adate,count(type) over(PARTITION BY empcode) AS Tot
FROM AttandanceTran,Empmast where Empmast.ECode=AttandanceTran.Ecode 
 and DATEPART(MM,ADate)=' + CAST(@imon as varchar(10)) + '
 and DATEPART(YYYY,ADate)=' + CAST(@iyear as varchar(10)) + ' )emp
PIVOT (Max(Type) FOR ADate IN (' + @cols + '))  AS Type'


-- Executing the query
EXEC(@qry)


-- Dropping temporary tables
DROP TABLE #Dates

end

解决方案

Main note is: Replace relationship based on WHERE clause with JOIN statement
Replace:

SELECT Empcode,Name,Type,Adate,count(type) over(PARTITION BY empcode) AS Tot
FROM AttandanceTran,Empmast where Empmast.ECode=AttandanceTran.Ecode
 and DATEPART(MM,ADate)=' + CAST(@imon as varchar(10)) + '
 and DATEPART(YYYY,ADate)=' + CAST(@iyear as varchar(10))

with:

SELECT em.Empcode, em.Name, em.Type, at.Adate, COUNT(at.type) AS Tot
FROM AttandanceTran AS at INNER JOIN Empmast AS em ON at.ECode=em.ECode
WHERE DATEPART(MM,ADate)=@iMon AND DATEPART(YYYY,ADate)=@iyear

Do you see the difference? I'm using JOIN[^] statement to create relationship between both tables. Have a look here: Visual Representation of SQL Joins[^]

If you would like to get Grand total for column and/or row, please see:
SQL - Pivot with Grand Total Column and Row[^]
Simple Way To Use Pivot In SQL Query[^]

这篇关于如何根据pivot中的条件对列进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！