آموزش Pivot و Unpivot در SQL Server با مثال Northwind راهنمای کامل، کاربردی و قابل‌استفاده

pivot sql server,pivot در sql,sql pivot example,sql server pivot tutorial,pivot query,pivot آموزش,pivot northwind,group by pivot,cross tab sql,pivot در sql server,unpivot در sql server,dynamic pivot sql,sql server northwind pivot,pivot unpivot آموزش,cross tab reports,sql data transformation,تبدیل ستون به سطر,گزارش ماتریسی sql dynamic sql pivot,sql unpivot example,مثال pivot فارسی,unpivot sql server,unpivot در sql,sql unpivot example,sql server unpivot tutorial,unpivot query,data unpivoting,sql flatten data,normalizing pivoted data,تبدیل ستون به سطر

فهرست مطالب

اگر در SQL Server با داده‌های عملیاتی، گزارش‌های مدیریتی یا مدل‌های تحلیلی سروکار دارید، دیر یا زود به نقطه‌ای می‌رسید که یک جدول طولانی و ستونی را باید تبدیل کنید به یک گزارش ماتریسی. اینجاست که Pivot وارد بازی می‌شود. پیوت کمک می‌کند داده‌های ردیفی را به ستون تبدیل کنید. در مقابل، Unpivot کاری می‌کند که ستون‌ها دوباره به ردیف تبدیل شوند.

بیایید بدون حاشیه سراغ اصل موضوع برویم. پیوت و آن‌پیوت را قدم‌به‌قدم، با مثال‌هایی که واقعاً به درد پروژه می‌خورند بررسی می‌کنیم. همه نمونه‌ها روی Northwind ساخته شده‌اند تا بتوانید همان لحظه تست کنید و نتیجه را ببینید.

۱. Pivot چیست و چرا این‌قدر مهم است؟

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

خروجی دلخواه رزومه‌ای شبیه این است:

کشور	۱۹۹۶	۱۹۹۷	۱۹۹۸
USA	۱۲۰	۱۴۵	۹۸
Germany	۹۰	۱۰۱	۷۷
Brazil	۳۰	۳۴	۲۰

داده خام اما در این قالب نیست. معمولاً دیتابیس‌ها داده‌ها را ردیفی ذخیره می‌کنند:

کشور	سال سفارش	OrderID
USA	۱۹۹۶	۱۲۳۴۵
USA	۱۹۹۶	۱۲۳۴۶
Germany	۱۹۹۷	۱۲۲۳۳

اینجاست که Pivot به کمک شما می‌آید.

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

۲. مثال پایه Pivot در Northwind

اول داده خام را انتخاب می‌کنیم:

SELECT 
    c.Country,
    YEAR(o.OrderDate) AS OrderYear,
    o.OrderID
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

حالا پیوت را اجرا می‌کنیم:

SELECT Country, [1996], [1997], [1998]
FROM (
    SELECT 
        c.Country,
        YEAR(o.OrderDate) AS OrderYear,
        o.OrderID
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
) AS SourceTable
PIVOT (
    COUNT(OrderID)
    FOR OrderYear IN ([1996], [1997], [1998])
) AS PivotTable
ORDER BY Country;

این شکل پیوت کلاسیک است. ساده، روشن و کاربردی.

۳. مشکل Pivot معمولی | ستون‌ها باید ثابت باشند

پیوت یک محدودیت مهم دارد:

باید مقدار ستون‌هایی که Pivot می‌شوند را صریح بنویسید.

یعنی [۱۹۹۶], [۱۹۹۷], [۱۹۹۸] باید دستی مشخص شوند.
اگر سال جدید اضافه شود، Pivot باید دوباره ویرایش شود.

برای همین Dynamic Pivot بسیار محبوب است.

۴. Dynamic Pivot — ستون‌ها خودکار ساخته می‌شوند

در دیتابیس Northwind فقط با چند خط کد می‌توانید سال‌ها را به‌صورت اتوماتیک استخراج کنید.

۴.۱ ساخت ستون‌های Pivot به‌صورت داینامیک

DECLARE @cols NVARCHAR(MAX);

SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(YEAR(OrderDate))
    FROM Orders
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,۱,۱,'');

۴.۲ ساخت کوئری Pivot داینامیک

DECLARE @query NVARCHAR(MAX);

SET @query = '
SELECT Country, ' + @cols + '
FROM (
    SELECT 
        c.Country,
        YEAR(o.OrderDate) AS OrderYear,
        o.OrderID
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
) AS SourceTable
PIVOT (
    COUNT(OrderID)
    FOR OrderYear IN (' + @cols + ')
) AS PivotTable
ORDER BY Country;';

EXEC sp_executesql @query;

با این روش هر مقدار جدیدی که در داده ظاهر شود، بدون تغییر حتی یک خط SQL، در گزارش می‌آید.

۵. مثال Dynamic Pivot برای مبلغ فروش

اگر بخواهید مجموع فروش را نشان دهید:

SUM(UnitPrice * Quantity)

نسخه داینامیک پیوت چنین می‌شود:

SELECT @query = '
SELECT Country, ' + @cols + '
FROM (
    SELECT 
        c.Country,
        YEAR(o.OrderDate) AS OrderYear,
        (od.UnitPrice * od.Quantity) AS TotalSale
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN [Order Details] od ON o.OrderID = od.OrderID
) AS SourceTable
PIVOT (
    SUM(TotalSale)
    FOR OrderYear IN (' + @cols + ')
) AS PivotTable
ORDER BY Country;';

EXEC sp_executesql @query;

این گزارش را معمولاً تیم مالی یا مدیریت برای تحلیل سالانه درخواست می‌کند.

۶. Unpivot — تبدیل ستون‌ها به سطر

اما گاهی عکس این عملیات لازم است.

فرض کنید گزارش زیر را دارید:

Product	Jan	Feb	Mar
Chai	۱۲۰	۱۰۰	۹۰
Chang	۸۰	۷۵	۶۵

و می‌خواهید دوباره آن را به شکل ردیفی تبدیل کنید:

Product	Month	Value
Chai	Jan	۱۲۰
Chai	Feb	۱۰۰
Chang	Mar	۶۵

برای این کار از Unpivot استفاده می‌کنیم.

مثال Unpivot روی داده ساختگی Northwind

SELECT ProductName, [Jan], [Feb], [Mar]
INTO #tmp
FROM (
    VALUES 
        ('Chai', 120, 100, 90),
        ('Chang', 80, 75, 65)
) AS T(ProductName, Jan, Feb, Mar);

اجرای Unpivot

SELECT ProductName, MonthName, SaleCount
FROM #tmp
UNPIVOT (
    SaleCount FOR MonthName IN ([Jan],[Feb],[Mar])
) AS U;

خروجی دقیقاً داده‌ها را به حالت اولیه برمی‌گرداند.

۷. Pivot یا Group By + Case کدام بهتر است؟

گاهی پیوت مناسب‌ترین راه نیست.
اگر ساختار گزارش ساده باشد، حتی این روش کارآمدتر است:

SELECT 
    Country,
    SUM(CASE WHEN YEAR(OrderDate) = 1996 THEN 1 END) AS C1996,
    SUM(CASE WHEN YEAR(OrderDate) = 1997 THEN 1 END) AS C1997,
    SUM(CASE WHEN YEAR(OrderDate) = 1998 THEN 1 END) AS C1998
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY Country;

این روش:

ساده‌تر است
خطا و پیچیدگی کمتری دارد
خوانایی بالاتری دارد
در برخی سناریوها سریع‌تر اجرا می‌شود

اما پیوت برای ساخت گزارش‌های ماتریسی با ستون‌های زیاد مناسب‌تر است.

۸. نکات بسیار مهم در Query Tuning Pivot

از ایندکس روی ستون‌های Grouping استفاده کنید

مثل OrderDate, CustomerID, Country

مراقب Cardinality باشید

اگر تعداد مقادیر ستون پیوت خیلی زیاد باشد، گزارش شما بیش از حد پهن می‌شود.

Dynamic Pivot همیشه امن نیست

اگر ورودی به صورت uncontrolled باشد، احتمال SQL Injection وجود دارد.

Pivot ذاتاً عملیات CPU-Intensive است

به‌خصوص اگر:

داده حجم بالا داشته باشد
منبع Pivot Joinهای متعدد داشته باشد

در این شرایط استفاده از Staging موقت در TempDB منطقی‌تر است.

۹. کاربردهای واقعی Pivot/Unpivot

در شرکت‌ها پیوت اغلب برای موارد زیر استفاده می‌شود:

گزارش فروش ماهانه
آنالیز روند سفارشات
گزارش تعداد مشتری به تفکیک کشور و سال
ساخت دیتاست گزارش‌های مالی
سنجش KPIها در بازه‌های زمانی
تولید Cross-Tab در Power BI قبل از بارگذاری
Data Flattening برای خروجی فایل‌های CSV یا Excel

Unpivot اغلب زمانی لازم است که:

داده‌ خام از Excel گرفته شده باشد
داده ورودی برای Power BI نامناسب باشد
دیتایی دارید که ستون‌های زیادی دارد
نیاز دارید ساختار تحلیل را استاندارد کنید

سوالات متداول FAQ

Pivot برای چه زمانی مناسب است؟

وقتی نیاز دارید داده‌ها در قالب جدول ماتریسی نمایش داده شود؛ معمولاً برای گزارش‌های مدیریتی یا تحلیل‌های سالانه.

Dynamic Pivot امن است؟

بله، اگر دقیق پیاده‌سازی شود و مقادیر پیوت از دیتابیس استخراج شوند.

Unpivot چه زمانی کاربرد دارد؟

وقتی داده شما ستون‌های متعدد دارد و می‌خواهید آن را دوباره به ساختار ردیفی استاندارد تبدیل کنید.

استفاده از پیوت سنگین است؟

در داده‌های بزرگ می‌تواند سنگین باشد؛ توصیه می‌شود از Stage Data یا TempDB استفاده کنید.

کدام سریع‌تر است: Pivot یا Group By + Case؟

در بسیاری موارد Group By سریع‌تر است؛ پیوت بیشتر برای خوانایی و ساختن جدول ماتریسی استفاده می‌شود.

تماس و مشاوره

اگر تیم شما به گزارش‌های پایدار، استاندارد و سریع نیاز دارد، یا می‌خواهید ساختار داده‌تان برای BI و تحلیل حرفه‌ای‌تر شود، تیم ما می‌تواند برای طراحی مدل داده، بهینه‌سازی کوئری‌ها و ساخت ساختارهای Pivot/Unpivot قابل نگهداری، در کنار شما باشد.

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

روزبه امیرعصامی

۱۴۰۴/۰۹/۲۲