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

 

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