SQL Cases Group By's Pivots Aggregates令人头疼

Question

在研究了很多不同的SQL语法之后，我仍然不太了解它。

我有一个表，数据是这样存储的：

LineNumber     Date            JobNumber      Task          Hours
------------------------------------------------------------------
 1             2016-08-15      100            Task 1          0.5
 1             2016-08-16      100            Task 1          2.0
 2             2016-08-15      200            Task 2          4.0
 3             2016-08-16      200            Task 3          1.5
 4             2016-08-17      200            Task 6          3.5

我要做的是格式化结果，使它们看起来像这样：

LineNumber      JobNumber        Task      Monday     Tuesday    Wednesday
--------------------------------------------------------------------------
1               100              Task 1      0.5        2.0         0.0
2               200              Task 2      4.0        0.0         0.0
3               200              Task 3      0.0        1.5         0.0
4               200              Task 6      0.0        0.0         3.5

使用CASE语句，我可以接近它。只是分组让我失望了。因为我需要对任务进行分组，但它们不需要，因为它们具有唯一的日期和小时值。

使用以下命令：

DECLARE @UserID int;
DECLARE @StartDate date;
DECLARE @MondayDate date;
DECLARE @TuesdayDate date;
DECLARE @WednesdayDate date;
DECLARE @ThursdayDate date;
DECLARE @FridayDate date;
DECLARE @SaturdayDate date;
DECLARE @SundayDate date;

SET @UserID = '1';
SET @StartDate = '2016-08-15';
SET @MondayDate = @StartDate;
SET @TuesdayDate = DATEADD(DAY, 1, @StartDate);
SET @WednesdayDate = DATEADD(DAY, 2, @StartDate);
SET @ThursdayDate = DATEADD(DAY, 3, @StartDate);
SET @FridayDate = DATEADD(DAY, 4, @StartDate);
SET @SaturdayDate = DATEADD(DAY, 5, @StartDate);
SET @SundayDate = DATEADD(DAY, 6, @StartDate);

select LineNumber, JobNumber, JobName, Task,
Monday = (CASE WHEN Date = @MondayDate THEN Hours ELSE '0.0' END),
Tuesday = (CASE WHEN Date = @TuesdayDate THEN Hours ELSE '0.0' END),
Wednesday = (CASE WHEN Date = @WednesdayDate THEN Hours ELSE '0.0' END),
Thursday = (CASE WHEN Date = @ThursdayDate THEN Hours ELSE '0.0' END),
Friday = (CASE WHEN Date = @FridayDate THEN Hours ELSE '0.0' END),
Saturday = (CASE WHEN Date = @SaturdayDate THEN Hours ELSE '0.0' END),
Sunday = (CASE WHEN Date = @SundayDate THEN Hours ELSE '0.0' END)

From v_TimesheetDataLines

WHERE Date >= @MondayDate and Date <= @SundayDate and UserID = @UserID
Order By LineNumber asc

我可以很接近，但我仍然会得到未分组的行，其中任务和作业编号相同。

我目前的结果是：

LineNumber      JobNumber        Task      Monday     Tuesday    Wednesday
--------------------------------------------------------------------------
1               100              Task 1     0.5        0.0       0.0
1               100              Task 1     0.0        2.0       0.0
2               200              Task 2     4.0        0.0       0.0
3               200              Task 3     0.0        1.5       0.0
4               200              Task 6     0.0        0.0       3.5

所以这两行在最终结果中都是第一行，我需要分组。它们将始终具有相同的工单编号和任务。

我尝试过各种CASE和嵌套选择，但都不能很好地实现。我在这里找到了其他一些关于pivots的帖子，我认为这可能是一个解决方案。不幸的是，我正在努力理解这些语句以及如何准确地使用它们。

任何答案\帮助都将一如既往的感谢。

Answer 1

通过LineNumber、JobNumber和任务使用GROUP BY。在工作日使用MAX (或SUM)。

编辑：

像this一样

Answer 2

供您参考：

顺便说一句，您可以在Group By参数中添加UserID，并在一次操作中完成所有操作。

Exec [prc-Pivot] 'Select * from YourData','DateName(WEEKDAY,Date)','sum(Hours)[]','LineNumber,JobNumber','count(*)[Records]'

返回

LineNumber  JobNumber   Records Monday    Tuesday   Wednesday
1           100         2       0.50      2.00      0.00
2           200         1       4.00      0.00      0.00
3           200         1       0.00      1.50      0.00
4           200         1       0.00      0.00      3.50

动态透视的存储过程

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'
--Exec [prc-Pivot] '#Temp','Attribute','max(Description)[]','ID','count(*)[Records]'

Set NoCount On

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)

**

-添加参数编辑

**

Declare @MondayDate Date = '2016-08-15'
Declare @SundayDate Date = '2016-08-21'
Declare @UserID int =  1
Declare @SQL varchar(max) = 'Select * from YourTable Where Date>='''+cast(@MondayDate as varchar(10))+''' and Date<='''+cast(@SundayDate as varchar(10))+''' and UserID='+cast(@UserID as varchar(25))

Exec [prc-Pivot] @SQL,'DateName(WEEKDAY,Date)','sum(Hours)[]','LineNumber,JobNumber','count(*)[Records]'

不确定SQL Server的版本是什么，但在构建SQL字符串时，我会选择连接