将 Access Crosstab/PIVOT 查询转换为 T-SQL [英] Convert Access Crosstab/PIVOT query to T-SQL

问题描述

我需要转换为在 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！