我需要转换为在SQL Server下工作的访问查询：
TRANSFORM Sum([T_Leads]![OrderType]='New Order')-1 & " / " & Sum([T_Leads]![OrderType]='Change Order')-1
AS [New / Change]
SELECT Employees.EmployeeName as Name, Count(T_Leads.OrderType) AS Total
FROM Employees INNER JOIN T_Leads ON Employees.EmployeeID = T_Leads.EmployeeID
WHERE (((T_Leads.Date)>Date()-7))
and [Employees.LeadRotation] <> "Inactive"
GROUP BY Employees.EmployeeName
ORDER BY T_Leads.Date
PIVOT T_Leads.Date;
输出显示当前担任潜在客户的员工列表（他们不是"非活跃"的）。对于列标题，将显示前七天的日期（如果在当天提交了潜在客户），并且在每个日期下显示两个总计。一个表示收到的新订单总数，另一个表示变更订单总数。我找不到任何生成日期列并显示两个值的示例在每列下。
Access查询当前在GridView:中生成这样的输出
+-------------+-------+----------+----------+----------+----------+-----------+
| Name | Total | 4/5/2016 | 4/6/2016 | 4/7/2016 | 4/8/2016 | 4/11/2016 |
+-------------+-------+----------+----------+----------+----------+-----------+
| Doe, Jane | 9 | 0/1 | 0/2 | 0/3 | / | 0/3 |
+-------------+-------+----------+----------+----------+----------+-----------+
| Guy, Some | 4 | 0/1 | 0/1 | / | / | 0/2 |
+-------------+-------+----------+----------+----------+----------+-----------+
| Doe, John | 10 | 0/1 | 1/1 | 2/1 | 0/3 | 0/1 |
样本数据：
| EmployeeID | Customer | Date | OrderType|
+-------------+------------------+------------+----------+
| 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 |
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;
这是使用动态枢轴。您可能希望在应用程序端或报告端执行此业务逻辑，而不是复杂的sql。
您需要动态生成数据透视列，然后为每个数据透视列执行case语句。以下是如何做到这一点的示例：
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 @startDate DATE = '2016-05-14', @cols VARCHAR(MAX) = '', @cols2 VARCHAR(MAX) = '';
SELECT @cols += ', CONVERT(VARCHAR(255), SUM(CASE WHEN O.Date = ''' + CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate)) + ''' AND O.OrderType = ''New'' THEN 1 ELSE 0 END)) + ''/'' + CONVERT(VARCHAR(255), SUM(CASE WHEN O.Date = ''' + CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate)) + ''' AND O.OrderType = ''Change'' THEN 1 ELSE 0 END)) ' + QUOTENAME(CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate), 103)) + CHAR(10) + CHAR(9) + CHAR(9)
, @cols2 += ', CASE WHEN ' + QUOTENAME(CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate), 103)) + ' = ''0/0'' THEN ''/'' ELSE ' + QUOTENAME(CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate), 103)) + ' END ' + QUOTENAME(CONVERT(VARCHAR(255), DATEADD(DD, X.Y, @startDate), 103)) + CHAR(10) + CHAR(9)
FROM (VALUES (0),(-1),(-2),(-3),(-4),(-5),(-6)) X(Y)
JOIN tmpOrders_Test O ON O.Date = DATEADD(DD, X.Y, @startDate)
GROUP BY X.Y
ORDER BY X.Y;
DECLARE @SQL VARCHAR(MAX) = '
WITH T AS (
SELECT E.EmployeeID
, COUNT(*) Total
' + @cols + '
FROM tmpEmployees_Test E
JOIN tmpOrders_Test O ON O.EmployeeID = E.EmployeeID
WHERE O.Date BETWEEN ''' + CONVERT(VARCHAR(255), DATEADD(dd, -6, @startDate)) + ''' AND ''' + CONVERT(VARCHAR(255), @startDate) + '''
GROUP BY E.EmployeeID)
SELECT E.EmployeeName
, Total
' + @cols2 + '
FROM T
JOIN tmpEmployees_Test E ON E.EmployeeID = T.EmployeeID;'
--PRINT @SQL;
EXEC(@SQL);
这反映了您期望的输出（据我所知），即使它看起来有点混乱。不过，我不认为你能在没有一点混乱的情况下产生你想要的输出。
注意：动态SQL中的CTE只是去掉所有的"0/0"并使其为"/"，这似乎是最简单的方法。
目前，交叉表对于轻松的透视转换来说并不简单，因为您有两个条件聚合，然后将它们连接到字符串值中。此外，您还有一个聚合组计数。
在TSQL中，这个查询会有点复杂。考虑以下方法，使用两个派生的表透视聚合查询，并使用CAST连接在一起。
来源 （临时表格-感谢@ZLK）
CREATE TABLE #tmpOrders (EmployeeID INT, Customer VARCHAR(255),
Date DATE, OrderType VARCHAR(255));
INSERT #tmpOrders (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');
查询（两个枢轴的内部连接）
SELECT pvt1.Name, pvt1.Total,
CAST(pvt1.[2016-05-12] AS VARCHAR(2)) + '/'
+ CAST(pvt2.[2016-05-12] AS VARCHAR(2)) As [05/12/2016],
CAST(pvt1.[2016-05-13] AS VARCHAR(2)) + '/'
+ CAST(pvt2.[2016-05-13] AS VARCHAR(2)) As [05/13/2016],
CAST(pvt1.[2016-05-14] AS VARCHAR(2)) + '/'
+ CAST(pvt2.[2016-05-14] AS VARCHAR(2)) As [05/14/2016]
FROM
(SELECT t.Customer as Name, t.[Date],
COUNT(*) As Total,
SUM(CASE WHEN t.OrderType = 'New' THEN 1 ELSE NULL END) AS NewOrders
FROM #tmpOrders t
WHERE (t.[Date] > GETDATE()-7)
GROUP BY t.Customer, t.[Date]) AS src1
PIVOT
(
COUNT(src1.[NewOrders])
FOR src1.[Date] IN ([2016-05-12], [2016-05-13], [2016-05-14])
) AS pvt1
INNER JOIN
(SELECT t.Customer as Name, t.[Date],
COUNT(*) As Total,
SUM(CASE WHEN t.OrderType = 'Change' THEN 1 ELSE NULL END) AS ChangeOrders
FROM #tmpOrders t
WHERE (t.[Date] > GETDATE()-7)
GROUP BY t.Customer, t.[Date]) AS src1
PIVOT
(
COUNT(src1.[ChangeOrders])
FOR src1.[Date] IN ([2016-05-12], [2016-05-13], [2016-05-14])
) AS pvt2
ON pvt1.Name = pvt2.Name
输出
--Name Total 05/12/2016 05/13/2016 05/14/2016
--Amazing Things 1 0/1 0/0 0/0
--Cool Things 1 0/1 0/0 0/0
--Fake Customer 1 0/0 0/0 1/0
--Some Company 1 0/0 0/1 0/0
--Stuff Inc. 1 0/0 0/0 1/0
请注意：在翻译过程中，您会丢失Access交叉表查询的动态质量，该查询将输出所有现有值（最多255列）。在SQL Server中，对于常规枢轴，您需要提前声明这些值，或者使用存储的procs/函数动态返回所有值。