SQL Server PIVOT UNPIVOT تغییر شکل داده ها TSQL

تغییر شکل داده‌ها در SQL Server: راهنمای کامل PIVOT و UNPIVOT با T-SQL

SQL Server دو عملگر رابطه‌ای بسیار مهم به نام‌های PIVOT و UNPIVOT را فراهم می‌کند که به ما امکان می‌دهند عبارات جدولی را از یک فرم به فرم دیگر تبدیل کنیم. عملگر PIVOT سطرها را به ستون‌ها (چرخش داده) تبدیل می‌کند و عملگر UNPIVOT ستون‌ها را به سطرها تبدیل می‌کند. این عملگرها هنگام نوشتن کوئری‌های پیچیده SQL Server برای تولید گزارش‌ها در فرمت‌های مختلف و تحلیل داده بسیار مفید هستند. در این مقاله، نحوه استفاده از هر دو PIVOT و UNPIVOT را با استفاده از نمونه کدهای T-SQL بررسی خواهیم کرد.

عملگر PIVOT برای تبدیل سطرها به ستون‌ها استفاده می‌شود. اجازه دهید یک جدول نمونه و مقداری داده برای نمایش این موضوع ایجاد کنیم.

CREATE TABLE SalesData (
    SalesPerson VARCHAR(50),
    Product VARCHAR(50),
    SalesAmount INT
);

INSERT INTO SalesData (SalesPerson, Product, SalesAmount) VALUES
('John', 'Laptop', 1000),
('John', 'Monitor', 200),
('John', 'Keyboard', 50),
('Mike', 'Laptop', 1200),
('Mike', 'Monitor', 250),
('Sarah', 'Laptop', 900),
('Sarah', 'Mouse', 30);

اکنون، بیایید این داده‌ها را PIVOT کنیم تا فروش کل هر محصول را برای هر فروشنده نمایش دهیم.

ساختار کلی عملگر PIVOT به این شکل است:

PIVOT (AggregateFunction (ValueColumn) FOR PivotColumn IN ([Column1],[Column2],[ColumnN]))

در این ساختار:

• AggregateFunction (ValueColumn): تابعی تجمعی (مانند SUM, COUNT, AVG, MAX, MIN) که روی ستون مقادیر اعمال می‌شود.
• PivotColumn: ستونی که مقادیر آن به عنوان نام ستون‌های جدید تبدیل می‌شوند.
• IN ([Column1],[Column2],[ColumnN]): لیستی از مقادیر منحصر به فرد از PivotColumn که قرار است به ستون‌های جدید تبدیل شوند.

حالا نمونه کد PIVOT را ببینید:

SELECT SalesPerson,
       ISNULL(Laptop, 0) AS LaptopSales,
       ISNULL(Monitor, 0) AS MonitorSales,
       ISNULL(Keyboard, 0) AS KeyboardSales,
       ISNULL(Mouse, 0) AS MouseSales
FROM (
    SELECT SalesPerson, Product, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Product IN ([Laptop], [Monitor], [Keyboard], [Mouse])
) AS PivotTable;

این کوئری سطرها از داده‌های فروش برای هر محصول را به ستون‌ها تبدیل می‌کند و یک نمای خلاصه از فروش بر اساس محصول برای هر فروشنده ارائه می‌دهد که برای گزارش‌گیری SQL بسیار کارآمد است.

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

CREATE TABLE PivotedSales (
    SalesPerson VARCHAR(50),
    Laptop INT,
    Monitor INT,
    Keyboard INT,
    Mouse INT
);

INSERT INTO PivotedSales (SalesPerson, Laptop, Monitor, Keyboard, Mouse) VALUES
('John', 1000, 200, 50, 0),
('Mike', 1200, 250, 0, 0),
('Sarah', 900, 0, 0, 30);

اکنون، بیایید این داده‌ها را UNPIVOT کنیم تا ستون‌های محصول را دوباره به سطرها تبدیل کنیم.

ساختار کلی عملگر UNPIVOT به این شکل است:

UNPIVOT (ValueColumn FOR PivotColumn IN ([Column1],[Column2],[ColumnN]))

در این ساختار:

• ValueColumn: ستون جدیدی که مقادیر از ستون‌های PivotColumn اصلی در آن قرار می‌گیرند.
• PivotColumn: ستون جدیدی که نام ستون‌های اصلی از IN ([Column1],[Column2],[ColumnN]) در آن قرار می‌گیرد.
• IN ([Column1],[Column2],[ColumnN]): لیستی از ستون‌هایی که قرار است به سطرها تبدیل شوند.

حالا نمونه کد UNPIVOT را ببینید:

SELECT SalesPerson, Product, SalesAmount
FROM PivotedSales
UNPIVOT (
    SalesAmount FOR Product IN ([Laptop], [Monitor], [Keyboard], [Mouse])
) AS UnpivotedTable;

این کوئری ستون‌های محصول را دوباره به سطرها تبدیل می‌کند و داده‌ها را به فرمت سطرگرا (مشابه فرمت اولیه خود) باز می‌گرداند که برای نرمال‌سازی داده‌ها یا آماده‌سازی برای تحلیل‌های دیگر مفید است.

هنگامی که در زمان طراحی، نام دقیق ستون‌ها برای عبارت IN در PIVOT را نمی‌دانید، می‌توانید از SQL دینامیک برای ساخت کوئری PIVOT استفاده کنید. این روش به ویژه زمانی مفید است که داده‌ها یا دسته‌ها به طور مکرر تغییر می‌کنند و نیاز به تحلیل داده SQL پویا دارید.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Get distinct product names for the PIVOT IN clause
SELECT @columns = STUFF((SELECT ',' + QUOTENAME(Product)
                                FROM SalesData
                                GROUP BY Product
                                ORDER BY Product
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Construct the dynamic PIVOT query
SET @sql = N'
SELECT SalesPerson, ' + @columns + '
FROM (
    SELECT SalesPerson, Product, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Product IN (' + @columns + ')
) AS PivotTable
ORDER BY SalesPerson;';

-- Execute the dynamic query
EXEC sp_executesql @sql;

این کوئری PIVOT دینامیک به طور خودکار محصولات متمایز را شناسایی کرده و دستور PIVOT را می‌سازد که آن را برای ساختارهای داده‌ای متغیر، انعطاف‌پذیر می‌کند. این تکنیک برای گزارش‌گیری پیشرفته SQL Server و وظایف تحلیل داده که در آن‌ها سرفصل ستون‌ها ممکن است ثابت نباشند، بسیار حیاتی است.

در پایان، جداول ایجاد شده را پاکسازی می‌کنیم تا فضای پایگاه داده مرتب بماند:

DROP TABLE SalesData;
DROP TABLE PivotedSales;