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

در قسمت اول، با ساختار PIVOT و نحوه چگونگی استفاده از آن آشنا شدید.
این بار به سراغ دیتابیس معروف Northwind می رویم و می خواهیم بدانیم در جدول Customers به ازای کشورهای uk، spain و usa چه تعداد کارمند داریم.
بنابراین در ابتدای کار می بایست تکلیف سه مورد زیر را مشخص کنیم:
1) Aggregate Column: فیلد CustomerID
2) PIVOT Column: فیلد Country
3) لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد Country و شامل uk، spain و usa خواهد بود.

اسکریپت زیر را اجرا می کنیم:

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

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

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

[sql]
DECLARE @tblName VARCHAR(100),@fildName VARCHAR(100)
SET @tblName=N’Customers’
SET @fildName=N’CustomerID’
EXEC (‘SELECT ‘ +@fildName+’ FROM ‘+@tblName)
[/sql]

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

[sql]
DECLARE @country VARCHAR(MAX)
SET @country=”
SELECT @country=@country+Country+’,’ FROM Customers
GROUP BY Country
SET @country=LEFT(@country,LEN(@country)-1)
[/sql]

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

[sql]
EXEC(‘SELECT * FROM
(SELECT Country,customerID FROM Customers)C
PIVOT
(count(customerID) FOR Country
IN (‘+@country+’))pTable’)
[/sql]

و اما حالا شما به عنوان تمرین، تلاش کنید کوئری ای بنویسید که خروجی زیر را نمایش دهد. این خروجی قرار است گزارشی باشد که عناوین همه customerIDها در Header قرار گرفته و متناسب با هر کدام، CompanyNameشان نمایش داده شود.

امیدوارم این مقاله برای شما سودمند باشد.
در قسمت پایانی با مفهوم UNPIVOT آشنا خواهید شد.

شاید این موضوعات هم برات جالب باشه:

مفهوم JOIN در SQL (Inner, Left, Right و Full Join) PIVOT و UNPIVOT در SQL Server – قسمت اول دستور Select Distinct در SQL آشنایی با SQL Server 2016 Row Level Security – محدودیت ها و کارایی (بخش سوم)