数据表、透视表和过滤器

本文关键字：过滤器 透视 数据表 | 更新日期: 2023-09-27 18:18:19

我有一个这样的数据表:

SELECT TOP 1000 [ID]
  ,[NameMachine]
  ,[Reparation]
  ,[Substitution]
  ,[DataIn]
  ,[DataOut]
  ,[Operator]
  ,[TypeOperation]
  ,[Note]
  ,[Department] FROM [Man].[dbo].[Sheets]

我必须过滤它，所以我添加了这个参数:

SELECT ID, NameMachine, Reparation, Substitution, DataIn, DataOut, Operator, TypeOperation, Note, Department FROM dbo.Sheets WHERE Department = @Department AND year(DataIn) = @Year AND Operator =@Operator

现在，我有我的过滤数据表…但问题是:我怎么做一个这样的枢轴?

SELECT NameMachine, Operator, Department, [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] AS Tot, [1] AS Gen, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS Mag, [6] AS Giu, [7] AS Lug, [8] AS Ago, [9] AS Sett, [10] AS Ott, [11] AS Nov, [12] AS Dic
FROM (SELECT ID, NameMachine, datepart(month, DataInizio) AS Month, Operator, Department FROM Sheets) 
m PIVOT (count(ID) FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) pvt

我不知道如何在我的过滤数据表中做这样的查询…如何查询DataTable?

数据表、透视表和过滤器

这样如何:

SELECT NameMachine, Operator, Department, [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] AS Tot, [1] AS Gen, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS Mag, [6] AS Giu, [7] AS Lug, [8] AS Ago, [9] AS Sett, [10] AS Ott, [11] AS Nov, [12] AS Dic
FROM (SELECT ID, NameMachine, datepart(month, DataInizio) AS Month, Operator, Department FROM Sheets WHERE Department = @Department AND year(DataIn) = @Year AND Operator =@Operator) 
m PIVOT (count(ID) FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) pvt
相关文章：