PIVOT و UNPIVOT در SQL Server | تبدیل ردیف‌ها به ستون و بالعکس

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

مقدمه PIVOT و UNPIVOT در SQL Server

حال می خواهیم به عنوان مثال، اطلاعات فروش هر سال را به تفکیک هر فصل داشته باشیم. برای این کار از 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 قرار می‌گیرد.
3- لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد 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 شرکت داده می‌شود و نتایج مورد انتظارمان حاصل نخواهد شد. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.
برای رفع چنین مشکلی می‌بایست به جای استفاده از 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

 

حال می‌خواهیم به سراغ دیتابیس معروف Northwind برویم؛ می‌خواهیم بدانیم در جدول Customers به ازای کشورهای Uk، Spain و USA چه تعداد کارمند داریم.

بنابراین در ابتدای کار می بایست تکلیف سه مورد زیر را مشخص کنیم:

•  Aggregate Column: فیلد CustomerID
•  PIVOT Column: فیلد Country

 لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد Country و شامل uk، spain و usa خواهد بود.
 
اسکریپت زیر را اجرا می کنیم:

SELECT * FROM
(SELECT Country,CustomerID FROM Customers)C
PIVOT
(COUNT(CustomerID) FOR Country
IN ([uk],[usa],[spain]))pTable

خوب، تا این جای کار همه چیز مطابق با خواسته ما بود اما آیا شما می دانید که در جدول Customers چه کشورهایی وجود دارد؟ اگر تعداد این کشورها زیاد باشد، آیا منطقی است که پس از شناسایی آن ها، لیست عریض و طویلی از عنوان کشورها را در جلو IN و در ساختار PIVOT، ردیف کنیم؟ آیا این امکان وجود ندارد که در آینده عناوین کشورهای جدیدی به جدول مان اضافه شوند؟ و …
پاسخ مناسب به حل مشکلات مطرح شده، استفاده از Dynamic T-SQL خواهد بود.

Dynamic T-SQL در واقع اسکریپت هایی است که به صورت Dynamic ایجاد می کنیم و در همان لحظه، آن ها را اجرا می کنیم. با استفاده از Dynamic T-SQL می توان شرایطی پویا و متنوع در زمان اجرای یک کوئری ایجاد کرد. در اسکریپت زیر، متغیرهای مورد نیاز را تعریف و مقداردهی کرده و سپس با الحاق مناسبی با عبارات T-SQL، از طریق EXEC آنها را اجرا می کنیم. پیشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید.

اکنون برای آن که بتوانیم یک Dynamic PIVOT داشته باشیم، دقیقا از این تکنیک استفاده می کنیم. فرض می کنیم که می خواهیم بدانیم از هر کشور چه تعداد مشتری داریم. پس می بایست لیست کشورهای موجود را از جدول Customers استخراج کنیم.
 

DECLARE @country VARCHAR(MAX)
SET @country=''
SELECT @country=@country+Country+',' FROM Customers
GROUP BY Country
SET @country=LEFT(@country,LEN(@country)-1)

ابتدا متغیرcountry@ را تعریف می کنیم. در خط دوم اسکریپت بالا، ابتدا مقدار country@ را برابر Blank قرار می دهیم. توجه داشته باشید که اگر این کار را انجام ندهید با مشکل روبرو خواهید شد زیرا در ابتدا، مقدار country@ در هنگام تعریف، برابر با مفهوم NULL خواهد بود و همواره جمع یک رشته با NULL برابر با NULL خواهد شد!
در عبارت SELECT، تمامی کشورها را از طریق جدول Customers در متغیرcountry@ به همراه جداکننده ویرگول، ذخیره می کنیم. توجه داشته باشید که استفاده از GROUP BY به منظور جلوگیری از درج تکراری عناوین کشورها است. 
در خط آخر هم با توجه به این که در انتهای رشته ی country@ یک علامت ویرگول اضافی داریم، آن را حذف می کنیم. لیست تمامی کشورها در متغیر country@ ذخیره شده و می بایست آن را به عنوان لیست مورد جستجو در جلو عبارت IN در ساختار PIVOT قرار دهیم. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

EXEC('SELECT * FROM
(SELECT Country,customerID FROM Customers)C
PIVOT
(count(customerID) FOR Country
IN ('+@country+'))pTable')

سخن پایانی

PIVOT و UNPIVOT در SQL Server، حالا شما به عنوان تمرین، تلاش کنید کوئری ای بنویسید که خروجی بالا را نمایش دهد. این دستور در SQL Server برای دستور PIVOT برای تبدیل ردیف های جدول به ستون استفاده می شود، در حالی که عملگر UNPIVOT ستون ها را به ردیف تبدیل می کند. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.