Newbie:Creating a "crosstab" query

[kathryn]

I know that SQL can create a crosstab query and I have looked at Books On-line, but I still need help.

Here is what I have...

BranchStOffCode St OfficeType
AL001 AL CCO
AL002 AL BRANCH
AL004 AL CCO
AL007 AL BRANCH
AL009 AL BRANCH
TN001 TN BRANCH
TN002 TN BRANCH

and this is what I need:

State BranchCount CCOCount
AL 3 2
TN 2 0

In MS Access I can do this using a crosstab query. I think I need to use a subquery in SQL. The example in Books Online shows you how to manually insert values into a temp table if you know the values. My table has over 500 rows, so that is not an option.


Any resources, references, links would be greatly appreciated.

Thanks,

Kathryn

 

[kathryn]

I found the answer. Here it is for the archives

SELECT St,
SUM(CASE OfficeType WHEN 'Branch' THEN 1 ELSE 0 END) AS BranchCount,
SUM(CASE OfficeType WHEN 'CCO' THEN 1 ELSE 0 END) AS CCOCount
FROM tblBranch,tblOfficeType
WHERE tblbranch.officetypeid=tblOfficeType.officetypeid and
AddressEffDate <= getDate() AND
(AddressEndDate Is Null OR AddressEndDate >= getDate()) AND
(BranchClosedDate Is Null OR BranchClosedDate>=GetDate())
GROUP BY st


Kathryn