برخی موارد نیاز داریم که یک مقدار چند ستونی را به چند ستونی تبدیل کنیم. این مورد در ساخت گزارشات کاربرد زیادی دارد. به عنوان مثال فرض کنید یک کوئری آماده دارید که توسط آن لیستی از تمام شیفتهای افراد به همراه نام قسمتهای مربوطه در یک سازمان ارائه میشود. حال نیاز دارید بفهمید که هر کدام از قسمتهای سازمان در کدام شیفت، چند نفر کارمند دارد. عملا شما به یک جدول متقاطع نیاز دارید که ستونهای آن شامل نام قسمتهای مختلف سازمان و سطرهای آن نام شیفتهای مختلف میباشد.
ابتدا شما را با گرامر عبارت مورد نظر آشنا میکنم :
FROM table_source
-- جدول یا کوئری منبع
PIVOT (
aggregate_function ( value_column )
-- تابع تجمعی مورد نظر به همراه ستون مورد نظر
FOR pivot_column
-- ستونی که برای ساختن نام ستونها استفاده میشود
IN ( <column_list>)
-- مقادیری که از ستون بالا برای عملیات انتخاب میشود
) table_alias
-- نام مستعار جدول خروجی
حال این مثال را پیادهسازی میکنیم، لطفا به کوئری اولیه توجه بفرمایید :
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
خروجی این جدول لیستی از نام شیفت، شناسه کارمندان و نام قسمت مربوط میباشد. به خروجی آن توجه کنید:
ShiftName BusinessEntityID DepartmentName
Day 3 Engineering
Day 9 Engineering
...
Day 2 Marketing
Day 6 Marketing
...
Evening 25 Production
Evening 18 Production
Night 14 Production
Night 27 Production
...
Night 252 Production
(194 row(s) affected)
حال برای تغییر دادن این کوئری به خروجی گزارش مورد نظر آن را به صورت زیر تغییر دهید:
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
خروجی این کوئری به صورت زیر خواهد بود :
ShiftName Production Engineering Marketing
Day 79 6 9
Evening 54 0 0
Night 46 0 0
(3 row(s) affected)
