تبدیل سطر به ستون در SQL Server با CROSS APPLY Unpivot

تبدیل سطر به ستون در SQL Server با CROSS APPLY: راهنمای جامع Unpivot

اپراتورهای  در SQL Server برای تبدیل داده‌ها از سطر به ستون و بالعکس استفاده می‌شوند. اگرچه اپراتور PIVOT ساده و کاربرپسند است، اما اپراتور UNPIVOT به اندازه PIVOT انعطاف‌پذیر نیست زیرا انتظار دارد تعداد محدودی از ستون‌ها در دستور SELECT تبدیل (unpivot) شوند. در این مقاله، به بررسی روش **تبدیل سطر به ستون در SQL Server** با استفاده از اپراتور قدرتمند **CROSS APPLY** می‌پردازیم.

برای نمایش چگونگی عملکرد **Unpivot در SQL Server**، با ایجاد یک جدول نمونه حاوی داده‌های فروش سالانه شروع می‌کنیم. این جدول داده‌ها را به شکلی ذخیره می‌کند که هر سال یک ستون جداگانه است و ما قصد داریم این ستون‌ها را به سطر تبدیل کنیم.


CREATE TABLE YearlySales (
ProductID INT,
Year2021 DECIMAL(10, 2),
Year2022 DECIMAL(10, 2),
Year2023 DECIMAL(10, 2)
);

INSERT INTO YearlySales (ProductID, Year2021, Year2022, Year2023)
VALUES
(1, 100.50, 120.75, 150.00),
(2, 200.00, 210.25, 230.50),
(3, 50.25, 60.50, 70.75);


Unpivot با استفاده از اپراتور PIVOT

اپراتور PIVOT به طور گسترده‌ای برای تبدیل داده‌ها از سطر به ستون (پیوت کردن) استفاده می‌شود. اما اگر بخواهیم عملیات معکوس یعنی **تبدیل ستون به سطر** را انجام دهیم، می‌توانیم از UNPIVOT استفاده کنیم. در اینجا نحوه استفاده از اپراتور UNPIVOT را برای مجموعه داده‌هایمان مشاهده می‌کنید. توجه داشته باشید که این روش نیاز به تعریف صریح ستون‌هایی دارد که قرار است Unpivot شوند.


SELECT ProductID, SalesYear, SalesAmount
FROM YearlySales
UNPIVOT (SalesAmount FOR SalesYear IN (Year2021, Year2022, Year2023)) AS UnpivotedSales;


Unpivot با استفاده از CROSS APPLY

یکی از راه‌های انعطاف‌پذیرتر و قدرتمندتر برای انجام عملیات **Unpivot در SQL Server**، استفاده از عملگر **CROSS APPLY** است. این روش به ویژه زمانی که نیاز به Unpivot کردن تعداد متغیری از ستون‌ها یا انجام عملیات پیچیده‌تر روی داده‌ها دارید، مفید است. این روش به شما امکان می‌دهد تا هر ردیف را با یک تابع جدولی برای هر ستونی که می‌خواهید Unpivot کنید، مرتبط سازید.


SELECT
ys.ProductID,
T.SalesYear,
T.SalesAmount
FROM YearlySales ys
CROSS APPLY (
SELECT 'Year2021' AS SalesYear, ys.Year2021 AS SalesAmount
UNION ALL
SELECT 'Year2022' AS SalesYear, ys.Year2022 AS SalesAmount
UNION ALL
SELECT 'Year2023' AS SalesYear, ys.Year2023 AS SalesAmount
) AS T;


چرا CROSS APPLY برای Unpivot ارجحیت دارد؟

استفاده از **CROSS APPLY** برای **تبدیل ستون به سطر** چندین مزیت نسبت به اپراتور UNPIVOT سنتی دارد:

• **انعطاف‌پذیری بیشتر:** با **CROSS APPLY**، می‌توانید به راحتی ستون‌های بیشتری را برای Unpivot اضافه یا حذف کنید بدون اینکه نیاز به تغییر ساختار اصلی کوئری داشته باشید. این موضوع برای سناریوهایی که تعداد ستون‌ها پویا هستند بسیار مفید است.
• **کنترل بهتر بر نام ستون‌ها و انواع داده‌ها:** شما می‌توانید نام‌های دلخواه برای ستون‌های جدید (مانند SalesYear و SalesAmount) و همچنین نوع داده آن‌ها را تعریف کنید.
• **قابلیت مدیریت ستون‌های NULL:** CROSS APPLY به شما امکان می‌دهد تا مقادیر NULL را در فرآیند Unpivot به راحتی مدیریت کنید و تصمیم بگیرید که آیا می‌خواهید آن‌ها را شامل کنید یا خیر.
• **خوانایی و نگهداری آسان‌تر:** برای برخی توسعه‌دهندگان، ساختار **CROSS APPLY** شفاف‌تر و قابل درک‌تر است، به خصوص زمانی که چندین عملیات Unpivot یا منطق پیچیده درگیر باشد.
• **عملکرد در سناریوهای خاص:** در برخی موارد، CROSS APPLY می‌تواند عملکرد بهتری نسبت به UNPIVOT داشته باشد، به خصوص برای مجموعه‌ داده‌های بزرگتر یا سناریوهای پیچیده‌تر.

روش‌های مختلف Unpivot با CROSS APPLY

**CROSS APPLY** چندین راه برای **تبدیل داده‌های سطر به ستون** ارائه می‌دهد. در اینجا به بررسی سه روش رایج می‌پردازیم که همگی از قابلیت‌های **CROSS APPLY** برای **Unpivot داده‌ها در SQL Server** بهره می‌برند.

1. استفاده از بند VALUES

این روش، فشرده‌ترین راه برای **Unpivot داده‌ها** با استفاده از **CROSS APPLY** و بند **VALUES** است. این یک راه عالی برای **Unpivot داده‌ها در SQL Server** است، به خصوص اگر چندین ستون برای تبدیل دارید. هر ردیف از جدول اصلی با ردیف‌های تولید شده توسط بند **VALUES** که جفت‌های نام ستون و مقدار مربوطه را نگه‌داری می‌کند، اعمال می‌شود.


SELECT ProductID, SalesYear, SalesAmount
FROM YearlySales
CROSS APPLY (VALUES ('Year2021', Year2021), ('Year2022', Year2022), ('Year2023', Year2023)) AS UnpivotedData(SalesYear, SalesAmount);


2. استفاده از UNION ALL

روش **UNION ALL** همانند مثال اولیه **CROSS APPLY** است، اما با توضیح دقیق‌تر: شما هر ستونی را که می‌خواهید Unpivot کنید، با یک دستور SELECT جداگانه به همراه نام مستعار مناسب انتخاب می‌کنید و سپس آن‌ها را با **UNION ALL** ترکیب می‌کنید. این روش انعطاف‌پذیری زیادی در نام‌گذاری و مدیریت نوع داده فراهم می‌کند.


SELECT ProductID, SalesYear, SalesAmount
FROM YearlySales
CROSS APPLY (
SELECT 'Year2021' AS SalesYear, Year2021 AS SalesAmount
UNION ALL
SELECT 'Year2022' AS SalesYear, Year2022 AS SalesAmount
UNION ALL
SELECT 'Year2023' AS SalesYear, Year2023 AS SalesAmount
) AS UnpivotedData;


3. استفاده از چندین دستور SELECT (سازنده جدول مقادیر)

این روش شباهت زیادی به بند VALUES دارد، اما ساختار آن ممکن است برای برخی خواناتر باشد. در اینجا، شما هر جفت نام-مقدار را به عنوان یک ردیف جداگانه در یک سازنده جدول مقادیر مشخص می‌کنید. این روش نیز برای **Unpivot کردن ستون‌ها در SQL Server** بسیار کارآمد است و به شما امکان می‌دهد به راحتی ستون‌های جدید را تعریف کنید.


SELECT ProductID, SalesYear, SalesAmount
FROM YearlySales
CROSS APPLY (
SELECT 'Year2021', Year2021
UNION ALL SELECT 'Year2022', Year2022
UNION ALL SELECT 'Year2023', Year2023
) AS UnpivotedData(SalesYear, SalesAmount);


مقایسه عملکرد

عملکرد هر روش **Unpivot با CROSS APPLY** می‌تواند بسته به نسخه SQL Server، حجم داده‌ها و شاخص‌های موجود متفاوت باشد. در بیشتر موارد، استفاده از بند **VALUES** اغلب بهترین عملکرد را ارائه می‌دهد زیرا به طور موثرتری توسط بهینه‌ساز کوئری SQL Server مدیریت می‌شود. با این حال، مهم است که عملکرد را برای سناریوی خاص خود آزمایش کنید. در اینجا مثالی از یک کوئری ساده برای مقایسه عملکرد بین روش‌ها با استفاده از زمان اجرای آن‌ها آورده شده است:


-- برای مقایسه عملکرد، می‌توانید SET STATISTICS TIME ON را قبل از هر کوئری اجرا کنید.
SET STATISTICS TIME ON;

SET STATISTICS TIME OFF;