在T-sql中分组或旋转无论哪种方式我都需要帮助

Question

下午所有

好的，我一直在尝试为员工建立一个任务摘要，下面是我所做的：

SELECT CONCAT (Mpl.FName,' ',Mpl.SName) AS 'Name',
Ctt.TaskName AS 'Task',
Cts.Pickset,
SUM(omo.Qty) 'Total of tasks',
COUNT(Cts.TaskStart) 'Number of Tasks',
CAST(SUM(DATEADD(SECOND, DATEDIFF(SECOND, Cts.TaskStart, Cts.TaskEnd), 0)) AS TIME) AS 'Total Task Time'
FROM CHDS_Management.dbo.People Mpl
LEFT JOIN CHDS_Common.dbo.TaskScan Cts ON Cts.EmpID = Mpl.EmpNo
LEFT JOIN CHDS_Common.dbo.TaskType Ctt ON Ctt.TaskShort = Cts.Task
LEFT JOIN (SELECT picksetno, MAX(PicksetLineNo) AS 'Qty'  FROM CHDS_Common.dbo.OMOrder GROUP BY PicksetNo) omo ON omo.PicksetNo = Cts.Pickset
WHERE Cts.TaskStart >= '2017-06-01 06:00:00'
AND Cts.TaskEnd <= '2017-06-23 02:00:00'
GROUP BY CONCAT (Mpl.FName,' ',Mpl.SName),
ctt.TaskName,
Cts.Pickse

我想看到的是

> Justin Greenwood 
   > Pick 00:01:03
      > 123456 20/06/2017 13:45:45  20/06/2017 13:46:12 00:00:27
      > 123457 20/06/2017 13:47:14  20/06/2017 13:47:24 00:00:10
      > 123458 21/06/2017 15:17:25  21/06/2017 15:17:53 00:00:28
   > Systems 00:00:12
      > Systems 20/06/2017 13:45:05 20/06/2017 13:45:17 00:00:12

即使我不能得到钻头，我也想得到像这样的东西

Justin Pick    100(totel of the 3 tasks) 00:01:03 (time taken for all 3 tasks
Justin Systems 0                         00:00:12

Answer 1

您需要某种类型的报告系统来获得您想要的第一个结果--SQL不能以这种方式工作。这是一个与第二个类似的结果。我不知道你对‘3个任务的总和’的总和是多少，所以你需要提供更多的细节来得到更详细的答案。

SELECT CONCAT (
        Mpl.FName
        ,' '
        ,Mpl.SName
        ) AS 'Name'
    ,Ctt.TaskName AS 'Task'
    ,Cts.Pickset
    ,SUM(/*whatever you are totaling*/) 'Total of tasks'
    ,COUNT(Cts.TaskStart) 'Number of Tasks'
    ,convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, Cts.TaskStart, Cts.TaskEnd)), 0),  108) AS 'Total Task Time'
FROM CHDS_Management.dbo.People Mpl
LEFT JOIN CHDS_Common.dbo.TaskScan Cts ON Cts.EmpID = Mpl.EmpNo
LEFT JOIN CHDS_Common.dbo.TaskType Ctt ON Ctt.TaskShort = Cts.Task
WHERE Cts.TaskStart >= '2017-06-01 06:00:00'
    AND Cts.TaskEnd <= '2017-06-23 02:00:00'
GROUP BY CONCAT (
        Mpl.FName
        ,' '
        ,Mpl.SName
        )
    ,ctt.TaskName
    ,Cts.Pickset