Convert Access Crosstab Query to T-SQL Equivalent

Question

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?

score 1 · Accepted Answer · answered Jul 16 '13 at 16:07

1

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

answered Jul 16 '13 at 16:07

Taryn

242,637
56
362
405

do you have a sql server 2000/2005 equivalent (ie. case when)? – dotnetN00b Jun 18 '14 at 15:56
@dotnetN00b see this answer -- http://stackoverflow.com/a/15745076/426671 There is a version using a CASE expression – Taryn Jun 18 '14 at 15:57

Convert Access Crosstab Query to T-SQL Equivalent

1 Answers1