問題描述

我需要轉換為在 SQL Server 下工作的訪問查詢:

Access Query that I need to convert to work under 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;

輸出顯示當前擔任潛在客戶(非不活躍")的員工列表.對于列標題，會顯示前 7 天的日期(如果當天提交了潛在客戶)，并且每個日期下會顯示兩個總數.一個是收到的新訂單總數，另一個是變更訂單的總數.我找不到任何生成日期列并顯示兩個值的示例在每一列下.

The output displays a list of employees currently taking leads (who are not "inactive"). For the column headers, the date is shown for the previous seven days (if a lead was submitted on that day), and two totals are displayed under each date. One for the total number of New Orders received, and another for total number of Change Orders. I've not been able to find any examples that generate the date columns and display two values under each column.

Access 查詢當前在 GridView 中產生如下輸出:

The Access query currently produces output like this in a 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;

這是使用動態 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.

這篇關于將 Access Crosstab/PIVOT 查詢轉換為 T-SQL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！