You can pre-aggregate the results in the pivot source:
IF OBJECT_ID('TempDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)
INSERT INTO #Table
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,6 UNION ALL
SELECT 'Marketing',268638,6
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (Department VARCHAR(20), [1] INT,[2] INT, [3] INT,[4] INT,[5] INT,[6] INT,[7] INT,[8] INT,[9] INT,[10] INT,[11] INT,[12] INT)
INSERT INTO #Results
SELECT Department,
ISNULL([1],0),
ISNULL([2],0),
ISNULL([3],0),
ISNULL([4],0),
ISNULL([5],0),
ISNULL([6],0),
ISNULL([7],0),
ISNULL([8],0),
ISNULL([9],0),
ISNULL([10],0),
ISNULL([11],0),
ISNULL([12],0)
FROM (
SELECT Department, Month, CNT = COUNT(DISTINCT EmpId)
FROM #Table
GROUP BY Department, Month
) AS SRC
PIVOT (SUM(CNT) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
SELECT * FROM #Results
Does it do the trick for you?
-- Gianluca Sartori