Query의 Perfomance등의 분석은 하지 않겠습니다. 
Query를 짜는데 응용하시는데만... 



/*
--PIVOT PROTOTYPE

FROM table_source
PIVOT ( aggregate_function ( value_column )
		FOR pivot_column
			IN ( )
	   ) table_alias   
*/
USE ADVENTUREWORKS2008
GO 


SELECT 
	s.Name ShiftName,
	h.BusinessEntityID,
	d.Name DepartmentName
FROM 
	HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d 
	ON h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s 
	ON h.ShiftID = s.ShiftID
WHERE EndDate IS NULL AND
	d.Name IN ('Production', 'Engineering', 'Marketing')
ORDER BY ShiftName
GO 



--USING PIVOT 
SELECT 
	ShiftName,
	Production,
	Engineering,
	Marketing
FROM
	(SELECT 
		s.Name ShiftName,
		h.BusinessEntityID,
		d.Name DepartmentName
	FROM 
		HumanResources.EmployeeDepartmentHistory h
	INNER JOIN HumanResources.Department d 
		ON	h.DepartmentID = d.DepartmentID
	INNER JOIN HumanResources.Shift s 
		ON	h.ShiftID = s.ShiftID
	WHERE EndDate IS NULL AND
	d.Name IN ('Production', 'Engineering', 'Marketing')) AS a
PIVOT
	(
		COUNT(BusinessEntityID)
		FOR DepartmentName IN ([Production], [Engineering], [Marketing])
	) AS b
ORDER BY ShiftName
GO 

--USING CASE 
SELECT 
	ShiftName
	, Production = SUM(CASE WHEN DEPARTMENTNAME = 'PRODUCTION' THEN 1 ELSE 0 END) 
	, Engineering = SUM(CASE WHEN DEPARTMENTNAME = 'Engineering' THEN 1 ELSE 0 END) 
	, Marketing = SUM(CASE WHEN DEPARTMENTNAME = 'Marketing' THEN 1 ELSE 0 END) 
FROM
	(SELECT 
		s.Name ShiftName,
		h.BusinessEntityID,
		d.Name DepartmentName
	FROM 
		HumanResources.EmployeeDepartmentHistory h
	INNER JOIN HumanResources.Department d 
		ON	h.DepartmentID = d.DepartmentID
	INNER JOIN HumanResources.Shift s 
		ON	h.ShiftID = s.ShiftID
	WHERE EndDate IS NULL AND
	d.Name IN ('Production', 'Engineering', 'Marketing')) AS a
GROUP BY 
	SHIFTNAME 
go