PIVOT و UNPIVOT در SQL Server – قسمت اول

فرض کنيد جدولي با نام sale و مطابق با چنين ساختاري داريم. اين جدول شامل اطلاعات تعداد فروش (amount) بر اساس سال (year) و به ازاي هر فصل (quarter) است.

حال مي خواهيم به عنوان مثال، اطلاعات فروش هر سال را به تفکيک هر فصل داشته باشيم.
براي اين کار از Aggregation Functionها استفاده کرده و اسکريپت زير را اجرا مي کنيم:

SELECT year,quarter,SUM(amount) AS amountSum
FROM sale
GROUP BY YEAR,quarter
ORDER BY year
GO
 

خروجي کوئري بالا، اطلاعات فروش هر سال را به تفکيک هر فصل و در قالب يک رکورد نمايش می‌دهد.
در ادامه اگر بخواهيم اطلاعات فروش به ازاي هر سال و بر اساس تمامي فصل¬ها صرفا در قالب يک رکورد يا يک سطر نمايش داده شود، بايد چه کار کنيم؟
با استفاده از Sub Queryها اين کار امکان‌پذير است!

SELECT DISTINCT year
,(SELECT  SUM(amount)FROM sale s2 WHERE  s2.year=s1.year AND s2.quarter='spring') AS spring
FROM sale s1
GO
 

به خروجي کوئري اجرا شده توجه کنيد! اين کوئري، صرفا جهت نمايش اطلاعات فروش فصل بهار است. بنابراين براي نمايش اطلاعات ساير فصل ها، می‌بايست آنها را در کوئري شرکت داد:

SELECT DISTINCT year
,(SELECT SUM(amount)FROM sale s2 WHERE  s2.year=s1.year AND s2.quarter='spring') AS spring
,(SELECT SUM(amount)FROM sale s2 WHERE  s2.year=s1.year AND s2.quarter='summer') AS summer
,(SELECT SUM(amount)FROM sale s2 WHERE  s2.year=s1.year AND s2.quarter='autumn') AS autumn
,(SELECT SUM(amount)FROM sale s2 WHERE  s2.year=s1.year AND s2.quarter='winter') AS winter
FROM sale s1
GO
 

همان طور که می‌بينيد، توانستيم اطلاعات فروش در هر سال و به تفکيک هر فصل را در قالب يک رکورد نمايش دهيم اما نکته قابل تامل اين است که اگر تنوع بازه زماني اطلاعات فروش بر اساس ماه هاي مختلف در نظر گرفته شده بود آن گاه می‌بايست تمامي ماه هاي سال را در کوئري شرکت می‌داديم!

اين موضوع در خصوص موجوديت هايي متنوع، قطعا چالش برانگيز خواهد بود و روش بهينه اي به حساب نمی‌آيد.

اکنون براي رفع اين مشکل چه بايد کرد؟ پاسخ SQL Server استفاده از PIVOT Tableها است.

 
PIVOT Table چيست؟
همان طور که در شکل پایین می‌بينيد، خواسته ما، چرخش مقادير داده ها از درون ستون هاي جدول به سمت Header گزارش است و اين قابليت به کمک PIVOT Tableها در SQL Server تامين می‌شود. به عبارت ديگر زماني از PIVOT Tableها استفاده می‌کنيم که بخواهيم گزارش هايي از نوع Cross-Tab داشته باشيم.

الگوي استفاده از PIVOT Tableها در SQL به شکل زير می‌باشد:

SELECT <non-pivoted column>, 

    [first pivoted column] AS <column name>, 

    ... 

    [last 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], 

    ... [last pivoted column]) 

) AS <alias for the pivot table> 

<optional ORDER BY clause>;
 

بنابراين در ابتداي کار می‌بايست تکليف سه مورد زير را مشخص کنيم:
۱- Aggregate Column: همان فيلدي است که قرار است بر روي آن عمليات Aggregation انجام شود که در مثال فرضي ما، فيلد amount خواهد بود.
۲- PIVOT Column: فيلدي که قرار است از درون رکوردها به سمت Header گزارش چرخش داشته باشد که در مثال فرضي ما، فيلد quarter خواهد بود. اين فيلد در جلو عبارت FOR قرار می‌گيرد.
۳- ليستي که قرار است گزارش براساس آن تهيه شود که در مثال فرضي ما، مقادير فيلد quarter خواهد بود که همان spring,summer,autumn,winter خواهند بود.

اکنون با تشخيص موارد بالا، همه چيز براي ايجاد کوئري فراهم شده است:

SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable
 

شکل زير مقايسه ميان Plan اجرايي اين کوئري (استفاده از PIVOT) و کوئري قبلي (استفاده از Sub Query) را نشان می‌دهد و شما می‌بينيد که به لحاظ کارآيي، استفاده از PIVOT Tableها به چه ميزان تاثير گذار خواهند بود. مقايسه دياگرام Plan اجرايي اين دو کوئري هم در نوع خودش جالب توجه است. از طرفي ميزان خطوط نوشته شده در هر کوئري هم جاي تامل دارد!

ضمنا بايد به اين نکته هم توجه داشته باشيد که با استفاده از ايندکس گذاري مناسب، قطعا می‌توانيم به کارآيي بيشتر اين گونه کوئري ها کمک کنيم.
در ادمه می‌خواهيم تغييراتي بر روي جدول sale اعمال کنيم. اين تغييرات شامل افزودن يک فيلد از نوع INT و با خصوصيت IDENTITY است:

ALTER TABLE sale
ADD id INT IDENTITY
 

مجددا همان کوئري اي را که در آن از PIVOT استفاده شده بود، اجرا می‌کنيم.

خروجي کوئري، مطابق با آنچه که ما انتظارش را داشتيم، نيست!

آيا می‌توان چنين استنباط کرد که قابليت PIVOT صرفا براي جداول سه فيلدي ايجاد شده است؟ پاسخ مثبت و چنين برداشتي، قطعا موجب رنجش خاطر تيم توسعه دهنده Microsoft SQL Server خواهد شد!

 
اما بياييد با هم بررسي کنيم که چرا چنين اتفاقي افتاده و راه برون رفت از آن چيست؟

دوباره به کوئري زير توجه کنيد. فرض می‌کنيم هنوز به جدول مان فيلد id را اضافه نکرده ايم. کوئري زير را اجرا می‌کنيم:

SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable
 

در اين کوئري، SQL نتايج را بر اساس سال فروش (year) تفکيک کرده است. اما SQL از کجا تشخيص داده است که بايد چنين کاري را انجام بدهد؟ پاسخ آن است که در اين حالت تمامي فيلد هاي يک جدول به غير از Aggregate Column و PIVOT Column، توسط SQL در GROUP BY شرکت داده می‌شوند که در اين جا شامل فيلد year می‌شود.

 
اين موضوع در Plan اجرايي کوئري، به وضوح قابل مشاهده است.

البته اين قاعده در برخي از موارد به ضرر ما تمام می‌شود و اين همان جايي است که مثلا به جدول sale يک فيلد id اضافه شود. آن گاه علاوه بر فيلد year، فيلد id هم در GROUP BY شرکت داده می‌شود و نتايج مورد انتظارمان حاصل نخواهد شد.
براي رفع چنين مشکلي می‌بايست به جاي استفاده از SELECT ای مستقيم از جدول sale، با استفاده از يک Sub Query، فيلدهاي موردنظرمان را در SELECT انتخاب کنيم.

اسکريپت زير، نحوه انجام کار را به شما نشان می‌دهد:

SELECT * FROM
(SELECT year,quarter,amount FROM sale)s
PIVOT
(SUM(s.amount) FOR quarter
IN ([spring],[summer],[autumn],[winter])
)pTable
 

و اما حالا شما به عنوان تمرين، تلاش کنيد کوئري اي بنويسيد که خروجي زير را نمايش دهد:

اميدوارم اين مقاله براي شما سودمند باشد.
در قسمت بعدي مقاله، شما را با چالش جديدي در حوزه PIVOT روبرو خواهم کرد.

منبع: آموزش SQL Server