از - با استفاده از محوری و unpivot

شما می توانید از اپراتورهای رابطه محوری و Unpivot استفاده کنید تا یک عبارت با ارزش جدول را به جدول دیگری تغییر دهید. Pivot با تبدیل مقادیر منحصر به فرد از یک ستون در عبارت به چندین ستون در خروجی ، یک عبارت با ارزش را می چرخاند. و Pivot تجمع هایی را که در هر مقدار ستون باقی مانده که در خروجی نهایی تحت تعقیب هستند ، مورد نیاز است. Unpivot با چرخاندن ستون های یک عبارت با ارزش جدول به مقادیر ستون ، عمل مخالف را به محوریت انجام می دهد.

نحو برای محوری ارائه می دهد ساده تر و خواندنی تر از نحو است که در غیر این صورت ممکن است در یک سری پیچیده از انتخاب مشخص شود. اظهارات موردبرای توصیف کامل نحو برای محوری ، از (tranact-sql) مراجعه کنید.

نحو

نحو زیر نحوه استفاده از اپراتور Pivot را خلاصه می کند.

، [ستون محوری اول] را به عنوان ، [ستون محوری دوم] به عنوان ، انتخاب کنید.[آخرین ستون محوری] مانند () به عنوان Pivot (() برای [] در ([ستون محوری اول] ، [ستون محوری دوم] ،. [آخرین ستون محوری])) ؛ 

ملاحظات

شناسه های ستون در بند Unpivot از مجموعه کاتالوگ پیروی می کنند. برای پایگاه داده SQL ، جمع آوری همیشه sql_latin1_general_cp1_ci_as است. برای سرور SQL که تا حدی حاوی پایگاه داده ها است ، جمع آوری همیشه لاتین 1_general_100_ci_as_ks_ws_sc است. اگر ستون با ستون های دیگر ترکیب شود ، برای جلوگیری از درگیری ، یک بند جمع (Collate database_default) لازم است.

مثال اصلی محوری

مثال کد زیر یک جدول دو ستونی تولید می کند که دارای چهار ردیف است.

استفاده از AdventureWorks2014 ؛GO DaystomanuComature ، AVG (StandardCost) را به عنوان Averagecost از تولید. گروه محصول توسط DaystomanuCoucture انتخاب کنید. 

این مجموعه ی نتایج است.

DaystomanUcoucture Averagecost ----------------- ----------- 0 5. 0885 1 223. 88 2 359. 1082 4 949. 4105 

هیچ محصولی با سه تولید روز تعریف نشده است.

کد زیر نتیجه یکسان را نشان می دهد ، به گونه ای محوری است که مقادیر تولید روز به عنوان ستون تبدیل می شوند. یک ستون برای سه روز [3] ارائه می شود ، حتی اگر نتایج تهی باشد.

- جدول محوری با یک ردیف و پنج ستون "Averagecost" را به عنوان COST_SORTED_BY_PRODUCTION_DAYS ، [0] ، [1] ، [2] ، [3] ، [4] از (انتخاب DaystomanuUture ، StandardCost از تولید . product) به عنوان Pivotable SourceTable انتخاب کنید.(AVG (استاندارد) برای تولید Daystomanu در ([0] ، [1] ، [2] ، [3] ، [4])) به عنوان pivottable ؛ 

این مجموعه ی نتایج است.

cost_sorted_by_production_days 0 1 2 3 4 ---------------------------------- ------------- ----------- ------------ ------------- ----------- Averagecost 5. 0885 223. 88 359. 1082 NULL 949. 4105 

مثال پیچیده محوری

سناریوی متداول که در آن محور می تواند مفید باشد ، زمانی است که می خواهید گزارش های مربوط به جدول بندی را تهیه کنید تا خلاصه ای از داده ها را ارائه دهید. به عنوان مثال ، فرض کنید می خواهید از جدول BuyorderHeader در پایگاه داده نمونه AdventureWorks2014 پرس و جو کنید تا تعداد سفارشات خرید قرار داده شده توسط برخی از کارمندان را تعیین کنید. پرس و جو زیر این گزارش را که توسط فروشنده سفارش داده شده است ، ارائه می دهد.

استفاده از AdventureWorks2014 ؛GO SELECT VENDORID ، [250] به عنوان EMP1 ، [251] به عنوان EMP2 ، [256] به عنوان EMP3 ، [257] به عنوان EMP4 ، [260] به عنوان EMP5 از (SELECT RECTIONORDID ، EMPORMEEID ، VENDORID از خرید.(خریدارید) برای کارمندان در ([250] ، [251] ، [256] ، [257] ، [260])) به عنوان سفارش PVT توسط pvt. vendorid ؛ 

در اینجا یک مجموعه نتیجه جزئی وجود دارد.

Vendorid emp1 emp2 emp3 emp4 emp5 ----------- ----------- ----------- -----------:5 4 

نتایج برگشتی توسط این بیانیه زیر مجموعه بر روی ستون کارمندان محور است.

خرید خریدرید ، کارمند ، فروشنده از خرید و فروش را انتخاب کنید. 

مقادیر منحصر به فرد برگردانده شده توسط ستون کارمندان در مجموعه نتیجه نهایی به زمینه هایی تبدیل می شود. به همین ترتیب ، یک ستون برای هر شماره کارمند که در بند محوری مشخص شده است وجود دارد: در این مورد کارمندان 250 ، 251 ، 256 ، 257 و 260. ستون BuyOrderID به عنوان ستون Value ، که در برابر آن ستون های برگشتی در خروجی نهایی ، که ستون های گروه بندی نامیده می شوند ، قرار می گیرند ، گروه بندی می شوند. در این حالت ، ستون های گروه بندی با عملکرد شمارش جمع می شوند. توجه کنید که یک پیام هشدار دهنده ظاهر می شود که نشان می دهد هر مقدار تهی که در ستون BuyOrdorDID ظاهر می شود هنگام محاسبه شمارش برای هر کارمند در نظر گرفته نمی شود.

هنگامی که از توابع کل با محوری استفاده می شود ، هنگام محاسبه یک جمع ، وجود هر مقادیر تهی در ستون ارزش در نظر گرفته نمی شود.

مثال بی نظیر

Unpivot تقریباً با چرخاندن ستون ها به ردیف ، عملکرد معکوس Pivot را انجام می دهد. فرض کنید جدول تولید شده در مثال قبلی در پایگاه داده به عنوان PVT ذخیره می شود ، و شما می خواهید شناسه های ستون EMP1 ، EMP2 ، EMP3 ، EMP4 و EMP5 را به مقادیر ردیف بچرخانید که با یک فروشنده خاص مطابقت دارد. به همین ترتیب ، شما باید دو ستون اضافی را شناسایی کنید. ستونی که حاوی مقادیر ستونی است که شما می چرخید (emp1 ، emp2.) به عنوان کارمند خوانده می شود و ستونی که مقادیری را که در حال حاضر در زیر ستون های چرخاننده وجود دارد ، نگه می دارد ، سفارشات نامیده می شود. این ستون ها به ترتیب با Pivot_column و Value_column در تعریف Transact-SQL مطابقت دارند. در اینجا پرس و جو است.

- جدول را ایجاد کرده و مقادیر را همانطور که در مثال قبلی به تصویر کشیده شده است درج کنید. ایجاد جدول PVT (Vendorid Int ، EMP1 int ، EMP2 int ، EMP3 int ، emp4 int ، emp5 int) ؛وارد مقادیر PVT شوید (1،4،3،5،4،4) ؛درج مقادیر PVT (2،4،1،5،5،5) ؛درج مقادیر PVT (3،4،3،5،4،4) ؛درج مقادیر PVT (4،4،2،5،5،4) ؛درج مقادیر PVT (5،5،1،5،5،5) ؛برو - جدول را بدون نقص. انتخاب فروشنده ، کارمند ، سفارشات از (انتخاب فروشنده ، emp1 ، emp2 ، emp3 ، emp4 ، emp5 از pvt) p unpivot (سفارشات برای کارمند در (emp1 ، emp2 ، emp3 ، emp4 ، emp5)) به عنوان unpvt ؛رفتن 

در اینجا یک مجموعه نتیجه جزئی وجود دارد.

سفارشات کارمند فروش --------------------- ------ 1 EMP1 4 1 EMP2 3 1 EMP3 5 1 EMP4 4 1 EMP5 4 2 EMP1 4 2EMP2 1 2 EMP3 5 2 EMP4 5 2 EMP5 5. 

توجه کنید که Unpivot معکوس دقیق Pivot نیست. Pivot یک جمع را به همراه دارد و چندین ردیف احتمالی را در یک ردیف واحد در خروجی ادغام می کند. Unpivot نتیجه اصلی بیان با ارزش جدول را تولید نمی کند زیرا ردیف ها ادغام شده اند. همچنین ، مقادیر تهی در ورودی Unpivot در خروجی ناپدید می شوند. هنگامی که مقادیر ناپدید می شوند ، نشان می دهد که ممکن است مقادیر تهی اصلی در ورودی قبل از عملکرد محوری وجود داشته باشد.

نمای Sales. vsalespersonsalesbyfiscalyears در پایگاه داده نمونه AdventureWorks2019 از Pivot برای بازگشت کل فروش برای هر فروشنده ، برای هر سال مالی استفاده می کند. برای اسکریپت نمای در SQL Server Management Studio ، در Object Explorer ، نمای را در زیر پوشه Views برای پایگاه داده AdventureWorks2019 پیدا کنید. بر روی نام View راست کلیک کرده و سپس Script View را به عنوان انتخاب کنید.