SQL AdventureWorks count employees by gender by city

Question

I want to count the cities by gender, like this;

City    GenderFCount  GenderMCount
Redmond 10             20   

Here is my query gets city and gender in AdventureWorks database

select Gender,City from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID

If it is possible could you show the solution in many ways, like "PIVOT", by sql function(UDF), Stored Procedure or other ways.

thanks

Answer 1

Here is the PIVOT query, you can dump that in a stored procedure or udf

select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable

Example of the UDF

CREATE FUNCTION fnPivot()
RETURNS TABLE

AS
RETURN (
select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable
)
GO

now you can call it like this

 SELECT * FROM dbo.fnPivot()