Pivot :
این دستور العمل برای تبدیل مقادیر موجود در سطرها به ستونها و استفاده از توابع محاسباتی می باشد و عملیاتی مانند Cross-Tab در Excel را پیاده سازی می کند.
ساختار دستوری به شکل زیر می باشد:
Select Field1,Field2
From(
Table_Source
)
Pivot ( Aggregate Function(Value_Column)
For Pivot Column
In()
) Table Alias
در Select اول فیلدهایی را که در Column_list قسمت Pivot استفاده کرده و فیلدهایی که براساس آن میخواهیم اطلاعات گروه بندی شود را می آوریم.
در قسمت Table_Source که در واقع یک Derived Table هست ، اطلاعات مورد نیاز از جدولی که میخواهیم محاسبات را انجام دهیم فراخوانی می کنیم.
در قسمت Pivot تابع محاسباتی روی یکی از فیلدهای Select دوم صورت خواهد گرفت .
بخش For نام ستونی که میخواهیم مقادیر آن ستون را به ستونهای مختلف تبدیل کنیم می آوریم.
بخش In لیست مقادیری که میخواهیم به ستون تبدیل شود.
برای روشن شدن مطلب به مثال زیر توجه کنید:
یک جدول هدر فاکتور فروش با مشخصات زیر داریم:
Create Table HdrSale
( Id Int,
FactorDate char(8),
Cust_Name Varchar(30)
)
یک جدول ریز فاکتور فروش با مشخصات زیر داریم :
Create Table DetailSale
( Id Int,
IdHdrSale int,
Quantity int,
ProductCode int
)
در جدول اول اطلاعات زیر را اضافه می کنیم :
Insert into Hdrsale
values(1,'89/10/01','Mohammadi'),
(2,'89/10/02','Karimi'),
(3,'89/10/02','Sadeghi'),
(4,'89/10/01','Mohammadi'),
(5,'89/10/02','Karimi')
در جدول دوم اطلاعات زیر را وارد می کنیم :
Insert into DetailSale
Values(1,1,2,10),
(2,1,3,20),
(1,2,10,10),
(1,3,5,20),
(2,3,10,10),
(1,4,5,10),
(2,4,4,20),
(1,5,20,10)
حال اگر Query زیر را اجرا کنیم لیست تمام فاکتورهای مشتری در تاریخ های مختلف را خواهیم داشت .
Select Hs.FactorDate,Ds.Quantity,Hs.Cust_Name
From HdrSale Hs
Inner Join
DetailSale DS
On Ds.IdHdrSale=Hs.Id
حال ما میخواهیم به تفکیک افراد ببینیم در هر روز چه تعداد جنس فروخته شده است.
میخواهیم نتیجه زیر را داشته باشیم:
Date
|
Karimi
|
Sadeghi
|
Mohammadi
|
89/10/01
|
0
|
0
|
14
|
89/10/02
|
30
|
15
|
0
|
برای اینکار باید از Pivot استفاده کنیم:
با اجرای Query زیر نتیجه بالا بدست خواهد آمد :
Select FactorDate,Isnull([Karimi],0) as [Karimi],isNull([Mohammadi],0) as [Mohammadi],
IsNull([Sadeghi],0) as [Sadeghi]
From(
Select Hs.FactorDate,Ds.Quantity,Hs.Cust_Name
From HdrSale Hs
Inner Join
DetailSale DS
On Ds.IdHdrSale=Hs.Id
) As A
Pivot
(Sum(A.Quantity)
For Cust_Name In([Karimi],[Mohammadi],[Sadeghi])
) As B
Order by FactorDate
در Select دوم که داخل From هست هر فیلدی که از جداول بیاریم مانند فیلدهای جلوی Group By می ماند و نتیجه Query بدست آمده تغییر خواهد کرد. به طور مثال اگر فیلد Hs.id نیز اضافه شود دیگر نتیجه فوق را نخواهیم داشت و Sum براساس Group By براساس فیلدهای تاریخ و Id خواهد بود.
|