Я хочу посчитать города по полу, вот так;
City GenderFCount GenderMCount
Redmond 10 20
Вот мой запрос получает город и пол в базе данных AdventureWorks
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
Если возможно, не могли бы вы показать решение разными способами, например «PIVOT», с помощью функции sql (UDF), хранимой процедуры или другими способами.
спасибо
Вот запрос PIVOT, вы можете выгрузить его в хранимую процедуру или 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
Пример 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
Теперь вы можете вызвать его так
SELECT * FROM dbo.fnPivot()