SQL SERVER 2008

تجارب من در رابطه با SQL SERVER

استفاده از عملگر PIVOT برای تبدیل مقادیر تک ستونی به چند ستونی با کمک توابع تجمع
ساعت ۱۱:۱۱ ‎ق.ظ روز ۱۳۸۸/۱٢/۱٢  کلمات کلیدی: sql server ، pivot ، aggragate

برخی موارد نیاز داریم که یک مقدار چند ستونی را به چند ستونی تبدیل کنیم. این مورد در ساخت گزارشات کاربرد زیادی دارد. به عنوان مثال فرض کنید یک کوئری آماده دارید که توسط آن لیستی از تمام شیفت‌های افراد به همراه نام قسمت‌های مربوطه در یک سازمان ارائه می‌شود. حال نیاز دارید بفهمید که هر کدام از قسمت‌های سازمان در کدام شیفت، چند نفر کارمند دارد. عملا شما به یک جدول متقاطع نیاز دارید که ستون‌های آن شامل نام قسمت‌های مختلف سازمان و سطرهای آن نام شیفت‌های مختلف می‌باشد.
ابتدا شما را با گرامر عبارت مورد نظر آشنا میکنم :

 

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)