question about using TRANSFORM statement to convert select query 1

[psimon88]

Can I pick your brains about how to best transform the following from a select statement a transform. In an MDB, I would just use the crosstab query wizard but that's not an option in an ADP. Thanks.

SELECT dbo.VM_Users.FirstName, dbo.VM_Users.LastName, COUNT(dbo.ED_Caller_VM_Leads.lCallResult) AS [COUNT],
dbo.ED_Caller_VM_Leads.lCallResult AS DISP, LEFT(dbo.ED_Caller_VM_Leads.dtmED_Caller_TimeStamp, 11) AS DATE2
FROM dbo.ED_Caller_VM_Leads INNER JOIN
dbo.VM_Users ON dbo.ED_Caller_VM_Leads.lUserID = dbo.VM_Users.lUserID
GROUP BY dbo.VM_Users.FirstName, dbo.VM_Users.LastName, LEFT(dbo.ED_Caller_VM_Leads.dtmED_Caller_TimeStamp, 11),
dbo.ED_Caller_VM_Leads.lCallResult, DATEPART(mm, dbo.ED_Caller_VM_Leads.dtmED_Caller_TimeStamp)
HAVING (DATEPART(mm, dbo.ED_Caller_VM_Leads.dtmED_Caller_TimeStamp) = 8)

into a crosstab type of layout using the TRASFORM statement.

I'd like counts by rep by date by disposition (lcallresult). Right now, I take the data and throw it into a pivot table. I'd like each disposition to be a separate column with counts by date by rep.

 

[LeanneGodney]

Hi philaffinity,

I'm quite new to ADP's, but I've just spent some time creating a few pivot tables and pivot charts.

The way I set it up was to have a normal query grouping and summing/counting as you would normally do.

Then I created a form which I set up to display as a Pivot Table or Pivot chart. You can then drag columns into the relevant sections and right click them to modify their properties...

Does this all make sense?

What will you ultimately be using your crosstab query for?

Leanne

 

[psimon88]

Counts, averages, etc. Yeah, pivot forms are the way to go, I guess. Thans.

 

[cmmrfrds]

You can also simulate a pivot right in the SQL to a limited degree. For example, this uses the Northwind database so the code will run against that database.
SELECT CompanyName,
SUM((UnitPrice*Quantity)) As TotalAmt,
SUM(CASE When DatePart(mm,OrderDate) = 1 then (UnitPrice*Quantity) else 0 END) AS Jan,
SUM(CASE When DatePart(mm,OrderDate) = 2 then (UnitPrice*Quantity) else 0 END) AS Feb,
SUM(CASE When DatePart(mm,OrderDate) = 3 then (UnitPrice*Quantity) else 0 END) AS Mar,
SUM(CASE When DatePart(mm,OrderDate) = 4 then (UnitPrice*Quantity) else 0 END) AS Apr,
SUM(CASE When DatePart(mm,OrderDate) = 5 then (UnitPrice*Quantity) else 0 END) AS May,
SUM(CASE When DatePart(mm,OrderDate) = 6 then (UnitPrice*Quantity) else 0 END) AS Jun,
SUM(CASE When DatePart(mm,OrderDate) = 7 then (UnitPrice*Quantity) else 0 END) AS Jul,
SUM(CASE When DatePart(mm,OrderDate) = 8 then (UnitPrice*Quantity) else 0 END) AS Aug,
SUM(CASE When DatePart(mm,OrderDate) = 9 then (UnitPrice*Quantity) else 0 END) AS Sep,
SUM(CASE When DatePart(mm,OrderDate) = 10 then (UnitPrice*Quantity) else 0 END) AS Oct,
SUM(CASE When DatePart(mm,OrderDate) = 11 then (UnitPrice*Quantity) else 0 END) AS Nov,
SUM(CASE When DatePart(mm,OrderDate) = 12 then (UnitPrice*Quantity) else 0 END) AS Dec
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName

Here is another way to do the same thing with credit to
Rozenshtein.
SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) AS Jan,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) AS Feb,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) AS Mar,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) AS Apr,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) AS May,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) AS Jun,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) AS Jul,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) AS Aug,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) AS Sep,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))) AS Oct,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))) AS Nov,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12)))) AS Dec
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName

Url to Rozenshtein method.

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8