Pivot table ها در واقع همانند سایر Table های دیگر می باشند فقط با این تفاوت که در نمایش ردیف و ستون های آنها مقداری تفاوت وجود دارد .
دستور pivot جزو دستوراتی از Sql server می باشد که ممکن است در بیشتر پروژه هایمان برای نمایش گزارشات مختلف به استفاده از آن نیاز داشته باشیم . کاربرد دستور pivot تقریبا تبدیل نمایش خروجی یک کوئری از حالت ردیفی به ستونی می باشد . به صورت خیلی خلاصه بخواهیم نحوه کارکرد این دستور را بیان کنیم ، کار آن یک چرخش 90 درجه جداول می باشد ، یعنی جای ستون و ردیف ها را با هم جابه جا کنیم .
با یک مثال بهتر متوجه کارکرد این مثال خواهید شد . فرض کنید در دیتابیس خود یک جدول دارید که کل فروش محصولات خود را در داخل آن جدول نگه داری میکنیم ، حال قصد داریم یک کوئری بنویسیم که نمایش دهیم که در هر ماه ، از هر محصول چه تعداد فروش داشته ایم . که خروجی آن تقریبا شکلی شبیه به جدول زیر را خواهد داشت :
ساختار دستور Pivot به صورت زیر می باشد :
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
...
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(<aggregation function>(<column being aggregated>)
FOR [<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],...)
) AS <alias for the pivot table>
<optional ORDER BY clause>;
بعد از نوشتن Pivot و داخل پرانتز مقابل آن خواهیم داشت :
1- یک aggregation function .
2- نوشتن کلمه کلیدی For و تعیین نام فیلدی که قصد تبدیل آن به چنید ستون را خواهیم داشت .
همانطور که درsyntax این دستور ملاحضه می کنید ، دستور pivot حتما باید داری یک aggregation function باشد .
فیلد مقابل کلمه For با توجه به مقادیری که بر میگرداند هر کدام به ستون هایی مجزا تبدیل می شوند . مثلا اگر در مقابل for اسم ستونی را داریم که محتوای آن فیلد دارای سه مقدار A,B,C می باشد ، در نتیجه خروجی دستور شاهد سه ستون با عنوان های A,B,C خواهیم بود .
ساختار دستور آن به صورت خلاصه تر به صورت زیر می باشد :
Select Field1,Field2,...,Field n
From(
Table_Source
)
Pivot ( Aggregate Function(Value_Column)
For Pivot Column In(مقادیر مورد نظر)
) Table Alias
بررسی یک مثال عملی :
جدولی به اسم Invoice داریم که شامل سه فیلد به عنوان های زیر می باشد :
InvoiceNumber , InvoiceDate , InvoiceAmmount
ابتدا با استفاده از کوئری زیر جدول مورد نظر را ایجاد میکنیم :
create table Invoice
(
InvoiceNumber int primary key identity,
InvoiceDate date,
InvoiceAmount int
)
و چندین رکورد را در آن درج میکنیم . قصد داریم میزان فروش محصولات را در هرماه از سال نمایش دهیم . دستور مورد نظر را به صورت زیر می نویسیم :
SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot
که خروجی آن به صورت زیر می باشد :
و اما توضیح کدهای بالا :
در داخل دومین دستور Select که به صورت subquery نوشته شده است ، سه فیلد انتخاب کرده ایم که شامل :
select year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice
تا اینجا اگر این دستور را اجرا کنیم ، در خروجی شاهد سه ستون سال ، ماه و مقدار خواهیم بود .
فیلد سال که ثابت است و می خواهیم یکی از ستون های جدول باشد ، از Amount در قسمت Aggregate function از آن استفاده می کنیم و از month در قسمت for در دخل دستور pivot استفاده می کنیم .
مقدار ماه که دستور آن را به صورت زیر نوشته ایم :
left(datename(month,invoicedate),3)as [month]
یکی از مقادیر زیر را بر می گرداند :
jan, feb, mar, apr,may, jun, jul, aug, sep, oct, nov, dec
در قسمت For باید ستونی را که می خواهیم مقدار آن را به چندین ستون تقسیم کنیم را می نویسیم ،که در اینجا ماه فیلد ماه را نوشته ایم و بعد از آن کلمه کلیدی In را نوشته و داخل پرانتز مقادیری را که ستون مقابل For بر میگرداند را می نویسیم ، که در این مثال ایم 12 ماه میلادی را نوشته ایم چون فیلد month برای هر ردیف یکی از این 12 ماه را بر میگرداند .
اگر در مقابل دستور For و داخل In اسم هایی را بنویسیم که توسط فیلد مقابل For برگردانده نمی شود ، مقدار NULL را برای آن ستون خواهیم داشت .مثلا فرض کنید در مثال فوق علاوه بر اسم ماه ها ، دو فیلد اضافی دیگر هم با عنوان های esfand,bahman بنویسیم . چون این دو مقدار هیچ وقت از فیلد ماه با استفاده از دستوری که نوشتیم برگردانده نمی شوند ، پس در خروجی برای این دو فیلد شاهد مقدار NULL خواهیم بود .
نظر شما برای ما مهم است و به ما در بهبود سایت کمک میکند.