ستقدم هذه المقالة نظرة عامة كاملة حول استخدام عوامل تشغيل PIVOT وUNPIVOT في SQL Server. إن عوامل تشغيل PIVOT وUNPIVOT تشبه عوامل التشغيل العلائقية التي تسمح بذلك تحويل التعبير ذو القيمة الجدولية إلى جدول آخر . يقوم كلا المشغلين بإنشاء تقارير متعددة الأبعاد تساعد على دمج ومقارنة كمية كبيرة من البيانات بسرعة.

يمكننا استخدام عامل محوري عندما نحتاج إلى تحويل التعبيرات ذات القيمة الجدولية. إنه يقسم قيم فريدة من عمود واحد إلى عدة أعمدة في النتيجة النهائية. إنه أيضًا تجمعات قيم الأعمدة المتبقية المطلوبة في النتيجة النهائية. مشغل UNPIVOT يحول البيانات من أعمدة تعبير ذي قيمة جدولية إلى قيم أعمدة، وهو عكس PIVOT.

دعونا نفهم ذلك بمساعدة الرسم البياني البسيط الموضح أدناه:

على الجانب الأيسر من هذا الشكل، يمكننا أن نرى مجموعة البيانات الأصلية ، والذي يتكون من ثلاثة أعمدة: المنطقة, و مبيعات . بعد ذلك، يمكننا رؤية جدول PIVOT على الجانب الأيمن، والذي تم إنشاؤه عن طريق تدوير المنطقة (الصفوف) إلى الشمال والجنوب (الأعمدة) . بعد تحويل الصفوف إلى أعمدة، يمكننا القيام بذلك مجموع قيم عمود المبيعات لكل تقاطع بين أعمدة وصفوف جدول PIVOT.

دعونا أولا نقوم بإنشاء جدول اسمه Pivot_demo لإظهار مشغلي PIVOT وUNPIVOT. يقوم البيان التالي بإنشاء جدول جديد في قاعدة البيانات المحددة لدينا:

 CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int ) 

بعد ذلك، أدخل بعض البيانات في هذا الجدول على النحو التالي:

 INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500); 

يمكننا التحقق من البيانات باستخدام عبارة SELECT. سوف نحصل على الناتج أدناه:

مشغل محوري

يُستخدم هذا العامل لتدوير التعبيرات ذات القيمة الجدولية. تم تقديمه لأول مرة في إصدار SQL Server 2005. يقوم بتحويل البيانات من الصفوف إلى الأعمدة. فهو يقسم القيم الفريدة من عمود واحد إلى عدة أعمدة ثم يجمع قيم الأعمدة المتبقية المطلوبة في النتيجة النهائية.

يجب علينا اتباع الخطوات التالية لإنشاء جدول PIVOT:

• حدد مجموعة البيانات الأساسية للتمحور.
• قم بإنشاء نتائج مؤقتة بمساعدة جدول مشتق أو CTE (تعبير الجدول الشائع).
• الاستفادة من عامل PIVOT.

بناء الجملة

يوضح بناء الجملة التالي استخدام PIVOT في SQL Server:

 SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>

إذا كسرنا هذا البرنامج النصي، يمكننا أن نرى أنه يحتوي على قسمين منفصلين. يحدد القسم الأول البيانات من الجدول الرئيسي، ويحدد القسم الثاني كيفية إنشاء جدول PIVOT. يحتوي الجزء الثاني أيضًا على بعض الكلمات الأساسية الخاصة مثل SUM وFOR وIN. دعونا نرى معنى هذه الكلمات الرئيسية في عامل التشغيل PIVOT.

مجموع

يستخدم هذا المشغل ل تجميع القيم من العمود المحدد لاستخدامه في جدول PIVOT. يجب أن نستخدمه مع عامل PIVOT للحصول على الأعمدة المجمعة التي تعرض لأقسام القيم.

اجتياز الشجرة الثنائية داخل النظام

للكلمة الرئيسية

يتم استخدام هذه الكلمة الأساسية لبيان جدول PIVOT إرشاد المشغل PIVOT في أي عمود يجب تطبيق الدالة PIVOT. في الأساس، يشير إلى أسماء الأعمدة التي سيتم تحويلها من الصفوف إلى الأعمدة.

في الكلمة الرئيسية

هذه الكلمة الرئيسية يسرد كافة القيم الفريدة من عمود PIVOT ليتم عرضه كأعمدة جدول PIVOT.

مثال

دعونا نفهم ذلك بمساعدة الأمثلة المختلفة.

1. يحدد البيان التالي أولاً عمود السنة والشمال والجنوب كبيانات أساسية للمحور. بعد ذلك، قم بإنشاء نتيجة مؤقتة باستخدام الجدول المشتق ثم قم أخيرًا بتطبيق عامل PIVOT لإنشاء الإخراج النهائي. يتم ترتيب هذا الإخراج أيضًا في السنة التصاعدية.

 SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year 

سيؤدي تنفيذ هذا البيان إلى إنتاج الإخراج أدناه. وهنا يمكننا أن نرى المبلغ المحسوب لمبيعات المنطقة الشمالية والجنوبية المقابلة لقيم السنة .

2. هذا مثال آخر حيث سنقوم بحساب مجموع المبيعات لكل سنة بما يتوافق مع قيم المنطقة:

 SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region; 

تنفيذ هذا البيان سوف إنتاج خطأ لأننا لا نستطيع تحديد القيمة الرقمية كاسم عمود مباشرة.

ومع ذلك، يسمح لنا SQL Server بتجنب هذه المشكلة عن طريق استخدام الأقواس قبل كل قيمة عددية. يظهر البيان المحدث في مقتطف الكود التالي:

 SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region; 

تم تنفيذ هذا البيان بنجاح وعرض مجموع المبيعات المحسوب لكل سنة بما يتوافق مع قيم المنطقة:

3. المثال السابق للحصول على جدول PIVOT مفيد عندما نكون على دراية بجميع قيم أعمدة PIVOT المحتملة. ولكن لنفترض أنه في العام القادم، سيتم زيادة عدد الأعمدة. وبالنظر إلى المثال السابق، لدينا الأعوام 2010 و2011 و2012 كأعمدة محورية. ومع ذلك، ليس هناك ما يضمن أن هذه الأعمدة لن تتغير في المستقبل. ماذا يحدث إذا كانت لدينا بيانات من عام 2013 أو 2014، أو ربما أكثر من ذلك؟ في مثل هذه الحالات، سوف نحتاج إلى استخدام الجدول المحوري الديناميكي استعلامات لإصلاح هذه المشكلة.

يقوم استعلام جدول PIVOT الديناميكي بتغليف البرنامج النصي PIVOT بأكمله في إجراء مخزن. سيوفر هذا الإجراء خيارات قابلة للتعديل، مما يسمح لنا بتعديل متطلباتنا عن طريق تغيير بعض القيم ذات المعلمات.

تحويل من شار إلى int جافا

يشرح كود SQL التالي عمل جدول PIVOT الديناميكي. في هذا البرنامج النصي، قمنا أولاً باسترداد كافة القيم المميزة من عمود PIVOT، ثم قمنا بكتابة عبارة SQL للتنفيذ باستخدام استعلام PIVOT في وقت التشغيل. دعونا نرى الإخراج بعد تنفيذ هذا البرنامج النصي:

 CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END 

في هذا البرنامج النصي، قمنا بإنشاء اثنين من المتغيرات ذات المعلمات. ويرد وصفها أدناه:

@PivotColumn : سيأخذ هذا المتغير اسم العمود من الجدول الأصلي الذي تم إنشاء جدول PIVOT عليه. على سبيل المثال ، هنا يعرض عمود 'المنطقة' جميع المناطق المتوفرة في الأعمدة.

@PivotList : سيأخذ هذا المتغير قائمة الأعمدة التي نريد عرضها كعمود إخراج في جدول PIVOT.

تنفيذ الإجراء المخزن الديناميكي

بعد الإنشاء الناجح للإجراء المخزن الديناميكي، أصبحنا جاهزين لتنفيذه. يتم استخدام العبارة التالية لاستدعاء الإجراء الديناميكي المخزن لعرض جدول PIVOT في وقت التشغيل:

 EXEC DynamicPivotTable N'Region', N'[North], [South]' 

هنا قمنا الآن بتحديد اسم العمود ' منطقة ' كمعلمة أولى وقائمة الأعمدة PIVOT كمعلمة ثانية. سيؤدي تنفيذ البرنامج النصي إلى عرض الإخراج التالي:

قاعدة بيانات جافا Jdbc

الآن، يمكننا إضافة المزيد من الأعمدة في المستقبل في وقت التشغيل لعرض جدول PIVOT، وهو أمر غير ممكن مع المثالين الأولين.

مشغل أونبيفوت

إنها الطريقة العكسية لعامل PIVOT في SQL Server. يقوم هذا المشغل بتنفيذ العملية المعاكسة لـ PIVOT عن طريق تحويل البيانات من الأعمدة إلى الصفوف. يقوم مشغل UNPIVOT أيضًا بتدوير جدول PIVOT إلى الجدول العادي. تم تقديمه لأول مرة في إصدار SQL Server 2005.

بناء الجملة

يوضح بناء الجملة التالي UNPIVOT في SQL Server:

 SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name) 

مثال

دعونا نفهم كيفية UNPIVOT عملية PIVOT مع الأمثلة. سنقوم أولا بإنشاء الجدول الأصلي والجدول PIVOT ثم طبقت عامل تشغيل UNPIVOT على هذا الجدول.

يعلن مقتطف التعليمات البرمجية التالي أولاً عن متغير جدول مؤقت @Tab:

 DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) ) 

بعد ذلك، سوف نقوم بإدراج القيم في هذا الجدول على النحو التالي:

 INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year 

الآن، يمكننا تنفيذ عملية UNPIVOT باستخدام العبارة التالية:

 SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable 

سيؤدي تنفيذ مقتطف الشفرة إلى إرجاع المخرجات التالية:

يعد مقتطف التعليمات البرمجية أدناه مثالاً آخر لإجراء عملية PIVOT أولاً ثم عملية UNPIVOT على نفس الجدول ضمن استعلام واحد:

 SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable 

سيؤدي تنفيذ مقتطف الشفرة إلى عرض نفس الإخراج:

ملاحظة: عملية UNPIVOT هي عملية عكسية لإجراء PIVOT، ولكنها ليست عكسًا تامًا. نظرًا لأنه تم دمج الصفوف عندما يقوم PIVOT بحساب التجميع ودمج العديد من الصفوف في صف واحد في النتيجة، لذلك، لا يمكن لعملية UNPIVOT أن تجعل الجدول مثل الجدول الأصلي. ومع ذلك، إذا لم يقم عامل PIVOT بدمج العديد من الصفوف في صف واحد، فيمكن لمشغل UNPIVOT الحصول على الجدول الأصلي من إخراج PIVOT.

خاتمة

ستقدم هذه المقالة نظرة عامة كاملة على عوامل تشغيل PIVOT وUNPIVOT في SQL Server وتحويل تعبير جدول إلى آخر. يجب ألا ننسى أبدًا أن UNPIVOT هي العملية العكسية لـ PIVOT، ولكنها ليست العكس تمامًا لنتيجة PIVOT.