CODEWITHSUNDEEP
t-sqlms-accesspivottransformcrosstab
David
David

Reputation: 107

Convert Access Crosstab Query to T-SQL Equivalent

TRANSFORM Count(qryEAOCalls.CALLID) AS CountOfCALLID
SELECT qryEAOCalls.TAPSTAFFNAME, Count(qryEAOCalls.CALLID) AS [Total Calls]
FROM qryEAOCalls
WHERE qryEAOCalls.CALLDATE Between #1/1/1900# And Date()
GROUP BY qryEAOCalls.TAPSTAFFNAME
PIVOT qryEAOCalls.Status In ("Unassigned","Open","Closed","Follow-up Needed");

How do I convert this to T-SQL equivalent?

Upvotes: 1

Views: 1137

Answers (1)

Taryn
Taryn

Reputation: 247710

You should be able to use something similar to the following:

select TAPSTAFFNAME,
  Unassigned, Open, Closed, [Follow-up Needed],
  TotalCalls
from
(
  select e.TAPSTAFFNAME,
    e.CALLID,
    e.Status, 
    count(*) over(partition by e.TAPSTAFFNAME) TotalCalls
  from qryEAOCalls e
  where e.CALLDATE >= '1900-01-01'
    and e.CALLDATE <= getdate()
) src
pivot
(
  count(CALLID)
  for status in (Unassigned, Open, Closed, [Follow-up Needed])
) piv

Upvotes: 1

Related Questions