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>;

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

1- Aggregate Column: همان فيلدي است که قرار است بر روي آن عمليات Aggregation انجام شود که در مثال فرضي ما، فيلد amount خواهد بود.
2- 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 ستون ها را به ردیف تبدیل می کند. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.