IF OBJECT_ID('tmpEmployees_Test', 'U') IS NOT NULL DROP TABLE tmpEmployees_Test;
CREATE TABLE tmpEmployees_Test (EmployeeID INT, EmployeeName VARCHAR(255));
INSERT tmpEmployees_Test (EmployeeID, EmployeeName)
VALUES (1, 'Doe, Jane'), (2, 'Doe, John'), (3, 'Guy, Some');


IF OBJECT_ID('tmpOrders_Test', 'U') IS NOT NULL DROP TABLE tmpOrders_Test;
CREATE TABLE tmpOrders_Test (EmployeeID INT, Customer VARCHAR(255), Date DATE, OrderType VARCHAR(255));
INSERT tmpOrders_Test (EmployeeID, Customer, Date, OrderType)
VALUES (1, 'Fake Customer', '2016-05-14', 'New')
        , (2, 'Some Company', '2016-05-13', 'Change')
        , (3, 'Stuff Inc.', '2016-05-14', 'New')
        , (3, 'Cool Things', '2016-05-12', 'Change')
        , (3, 'Amazing Things', '2016-05-12', 'Change');


DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;
-- Kept it for formatting Purpose
DECLARE @columns1 NVARCHAR(MAX)
SET @columns1 = N'';
SELECT @columns1 += N', ISNULL(p.' + QUOTENAME(Name) + ',''/'') AS ' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;


SET @sql = N'
SELECT EmployeeName, Count(*) as Total  ' +  @columns1  + '
FROM
(
    SELECT  EmployeeID, EmployeeName' + ''+ @columns1 + '' + '
    FROM
    (
      SELECT    o.employeeID,EmployeeName, CAST(COUNT(case WHEN OrderType = ''New'' then 1  end) as varchar(5)) + ''/'' + 
                CAST(COUNT(case WHEN OrderType = ''Change'' then 1  end) as varchar(5)) as OrderType, CONVERT(nvarchar(30) , p.Date , 101) as Date
       FROM     dbo.tmpOrders_Test AS p
                    INNER JOIN dbo.tmpEmployees_Test AS o
                     ON p.EmployeeID = o.EmployeeID
       GROUP BY EmployeeName, Date, o.employeeID
    ) AS j
    PIVOT
    (
      Max(OrderType) FOR Date IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p) as p JOIN tmpOrders_Test as m on p.employeeID = m.employeeID
where [Date] > GETDATE()-7
GROUP BY EmployeeName ' + @columns + '
';

PRINT @sql;
EXEC sp_executesql @sql;

这是使用动态 Pivot.您可能希望在应用程序或报告端而不是复杂的 sql 上执行此业务逻辑.

This one is using dynamic Pivot. You might want to do this business logic on Application or Reporting Side instead of complex sql.