pivot Table در Sql Server قسمت 1
pivot چیست ؟
Pivot table ها در واقع همانند سایر Table های دیگر می باشند فقط با این تفاوت که در نمایش ردیف و ستون های آنها مقداری تفاوت وجود دارد .
دستور pivot جزو دستوراتی از Sql server می باشد که ممکن است در بیشتر پروژه هایمان برای نمایش گزارشات مختلف به استفاده از آن نیاز داشته باشیم.
کاربرد دستور pivot تقریبا تبدیل نمایش خروجی یک کوئری از حالت ردیفی به ستونی می باشد . به صورت خیلی خلاصه بخواهیم نحوه کارکرد این دستور را بیان کنیم ، کار آن یک چرخش 90 درجه جداول می باشد ، یعنی جای ستون و ردیف ها را با هم جابه جا کنیم .
با یک مثال بهتر متوجه کارکرد این مثال خواهید شد . فرض کنید در دیتابیس خود یک جدول دارید که کل فروش محصولات خود را در داخل آن جدول نگه داری میکنیم ، حال قصد داریم یک کوئری بنویسیم که نمایش دهیم که در هر ماه ، از هر محصول چه تعداد فروش داشته ایم . که خروجی آن تقریبا شکلی شبیه به جدول زیر را خواهد داشت :
ساختار دستور Pivot به صورت زیر می باشد :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 خواهیم بود.
ساختار دستور آن به صورت خلاصه تر به صورت زیر می باشد :
1 2 3 4 5 6 7 |
Select Field1,Field2,...,Field n From( Table_Source ) Pivot ( Aggregate Function(Value_Column) For Pivot Column In(مقادیر مورد نظر) ) Table Alias |
بررسی یک مثال عملی :
جدولی به اسم Invoice داریم که شامل سه فیلد به عنوان های زیر می باشد :
InvoiceNumber , InvoiceDate , InvoiceAmmount
ابتدا با استفاده از کوئری زیر جدول مورد نظر را ایجاد میکنیم :
1 2 3 4 5 6 |
create table Invoice ( InvoiceNumber int primary key identity, InvoiceDate date, InvoiceAmount int ) |
و چندین رکورد را در آن درج میکنیم . قصد داریم میزان فروش محصولات را در هرماه از سال نمایش دهیم . دستور مورد نظر را به صورت زیر می نویسیم :
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 نوشته شده است ، سه فیلد انتخاب کرده ایم که شامل :
1 |
select year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice |
تا اینجا اگر این دستور را اجرا کنیم ، در خروجی شاهد سه ستون سال ، ماه و مقدار خواهیم بود .
فیلد سال که ثابت است و می خواهیم یکی از ستون های جدول باشد ، از Amount در قسمت Aggregate function از آن استفاده می کنیم و از month در قسمت for در دخل دستور pivot استفاده می کنیم .
مقدار ماه که دستور آن را به صورت زیر نوشته ایم :
1 |
left(datename(month,invoicedate),3)as [month] |
یکی از مقادیر زیر را بر می گرداند :
1 |
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 خواهیم بود .
دیدگاهتان را بنویسید
برای نوشتن دیدگاه باید وارد بشوید.