AverageGeek AverageGeek - 7 months ago 9
SQL Question

Convert Access Crosstab/PIVOT query to T-SQL

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;


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.

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 |


Sample Data:

| 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 |

Answer
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;

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