ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

استفاده پیشرفته از PIVOT و UNPIVOT برای گزارش‌سازی در Oracle SQL

استفاده از PIVOT و UNPIVOT در Oracle

در دنیای گزارش‌سازی با Oracle SQL، تبدیل ساختار داده‌ها به فرم قابل فهم و بصری یکی از مهم‌ترین چالش‌هاست.

گاهی لازم است داده‌های ردیفی را به ستون تبدیل کنیم (مثلاً گزارش فروش هر سال در یک ستون مجزا)، یا برعکس، داده‌های ستونی را به شکل ردیفی نمایش دهیم (برای ذخیره‌سازی یا تحلیل آسان‌تر).

در این مقاله آموزش اوراکل در بخش آموزش اوراکل SQL به صورت کامل، با زبانی ساده و همراه با مثال‌های عملی، روش استفاده از دو قابلیت مهم Oracle SQL یعنی PIVOT و UNPIVOT را بررسی می‌کنیم.

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

آیا می‌خوای توابع تحلیلی (Analytical Functions) در Oracle بیشتر آشنا بشید ، پیشنهاد می شود نوشته زیر را مطالعه کنید:

در این نوشته شما می خوانید

معرفی مفاهیم PIVOT و UNPIVOT

  • PIVOT: این دستور به شما اجازه می‌دهد تا مقدارهای موجود در یک ستون خاص را به ستون‌های جدید تبدیل کرده و روی آن‌ها عملیات تجمیعی انجام دهید.

برای مثال، ستون “سال” می‌تواند به ستون‌های Y2022 و Y2023 تبدیل شود.

  • UNPIVOT: برعکس عمل Pivot، این دستور مقادیر ستون‌های متعدد را به یک ستون واحد تبدیل می‌کند. زمانی کاربرد دارد که بخواهید داده‌ها را از حالت گسترده به حالت نرمال برگردانید.

ساختار اولیه جدول فروش (sales)

				
					CREATE TABLE sales (
  region VARCHAR2(20),
  year NUMBER(4),
  amount NUMBER
);

INSERT INTO sales VALUES ('East', 2022, 1200);
INSERT INTO sales VALUES ('West', 2022, 900);
INSERT INTO sales VALUES ('East', 2023, 1400);
INSERT INTO sales VALUES ('West', 2023, 1100);

ساده‌ترین شکل استفاده از دستور PIVOT در Oracle SQL

هدف: نمایش مبلغ فروش هر منطقه به تفکیک سال در ستون‌های جداگانه.

				
					SELECT *
FROM (
  SELECT region, year, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR year IN (2022 AS Y2022, 2023 AS Y2023)
);

خروجی:

region Y2022 Y2023
East ۱۲۰۰ ۱۴۰۰
West ۹۰۰ ۱۱۰۰

Pivot چند تابعی با توابع آماری پیشرفته

				
					SELECT *
FROM (
  SELECT region, year, amount
  FROM sales
)
PIVOT (
  MAX(amount) AS max_amt,
  AVG(amount) AS avg_amt
  FOR year IN (2022 AS Y2022, 2023 AS Y2023)
);

خروجی:

region Y2022_MAX_AMT Y2022_AVG_AMT Y2023_MAX_AMT Y2023_AVG_AMT
East ۱۲۰۰ ۱۲۰۰ ۱۴۰۰ ۱۴۰۰
West ۹۰۰ ۹۰۰ ۱۱۰۰ ۱۱۰۰

شبیه‌سازی Pivot با CASE WHEN برای انعطاف بیشتر

اگر به Pivot داینامیک نیاز داشته باشید یا بخواهید ساختار گزارش را خودتان کنترل کنید:

				
					SELECT
  region,
  SUM(CASE WHEN year = 2022 THEN amount ELSE 0 END) AS Y2022,
  SUM(CASE WHEN year = 2023 THEN amount ELSE 0 END) AS Y2023
FROM sales
GROUP BY region;

این روش در شرایطی که تعداد مقادیر سال محدود و مشخص باشد، بسیار کاربردی و قابل کنترل است.

معرفی UNPIVOT: تبدیل ستون‌ها به ردیف

جدول فرضی زیر را در نظر بگیرید:

				
					CREATE TABLE quarterly_sales (
  employee VARCHAR2(20),
  Q1 NUMBER,
  Q2 NUMBER,
  Q3 NUMBER,
  Q4 NUMBER
);

INSERT INTO quarterly_sales VALUES ('Ali', 100, 150, 120, 130);

اجرای UNPIVOT:

				
					SELECT *
FROM (
  SELECT * FROM quarterly_sales
)
UNPIVOT (
  sales FOR quarter IN (Q1 AS 'Q1', Q2 AS 'Q2', Q3 AS 'Q3', Q4 AS 'Q4')
);

خروجی:

employee quarter sales
AliQ1۱۰۰
AliQ2۱۵۰
AliQ3۱۲۰
AliQ4۱۳۰

اجرای Pivot داینامیک در Oracle با PL/SQL

در Oracle برخلاف SQL Server نمی‌توان مستقیماً Pivot داینامیک نوشت.

اما با ترکیب LISTAGG و EXECUTE IMMEDIATE امکان‌پذیر است:

				
					DECLARE
  sql_stmt VARCHAR2(4000);
BEGIN
  SELECT 'SELECT * FROM (
            SELECT region, year, amount FROM sales
          )
          PIVOT (
            SUM(amount) FOR year IN (' || 
            LISTAGG(DISTINCT year, ',') WITHIN GROUP (ORDER BY year)
            || ')
          )'
  INTO sql_stmt
  FROM sales;

  EXECUTE IMMEDIATE sql_stmt;
END;

نکته: در Pivot داینامیک باید از قبل بدانید چه ستون‌هایی ایجاد خواهد شد یا از dynamic SQL برای پردازش خروجی استفاده کنید.

خطاهای رایج و نکات مهم در استفاده از PIVOT / UNPIVOT

  • استفاده از توابع تجمیعی الزامی است. (مانند SUM، MAX، COUNT)
  • مقدار تکراری برای ترکیب کلیدها بدون تابع تجمیعی باعث خطا می‌شود.
  • UNPIVOT فقط روی ستون‌هایی با نوع داده مشابه به درستی عمل می‌کند.
  • از ALIAS مناسب برای ستون‌ها در Pivot استفاده کنید تا خوانایی گزارش بالا برود.

کاربردهای واقعی در کسب‌وکارها و سازمان‌ها

  • ساخت گزارش فروش سالانه/ماهیانه به تفکیک منطقه، محصول، یا نماینده فروش
  • تجزیه و تحلیل رفتار مشتریان در فصول مختلف سال
  • نمایش داده‌ها به شکل نمودار محوری (Cross Tab) برای ارائه مدیریتی
  • استخراج داده برای داشبوردهای گرافیکی در ابزارهایی مانند Power BI و Oracle Analytics

سوالات متداول درباره دستورات PIVOT و UNPIVOT در Oracle

Oracle به‌صورت مستقیم از Pivot داینامیک پشتیبانی نمی‌کند، اما می‌توان با استفاده از PL/SQL و تابع LISTAGG همراه با EXECUTE IMMEDIATE کوئری Pivot را به‌صورت داینامیک ایجاد و اجرا کرد.

زمانی‌که داده‌ها به‌صورت ستونی ذخیره شده‌اند (مانند Q1 تا Q4) ولی برای پردازش، گزارش‌سازی یا تحلیل نیاز به ساختار ردیفی داریم (مانند ستون‌های quarter و sales)،

UNPIVOT بسیار کاربردی است. مثلاً هنگام آماده‌سازی داده‌ها برای تحلیل در Power BI یا Tableau.

هر دو روش نتایج مشابهی ایجاد می‌کنند اما:

  • PIVOT ساختاری تمیز و مستقیم دارد و برای Pivot ساده بسیار مناسب است.
  • استفاده از CASE WHEN انعطاف بیشتری برای شرایط پیچیده و چند لایه دارد (مثلاً فیلترهای اضافی، توابع شرطی، محاسبات سفارشی).

Oracle در دستور PIVOT الزام می‌کند که از تابع تجمیعی مانند SUM, MAX, AVG, COUNT و… استفاده شود.

اگر از تابع تجمیعی استفاده نشود یا داده‌های تکراری بدون تجمیع باشند، این خطا نمایش داده می‌شود. همیشه باید یک Aggregation تعریف کنید.

📦 جمع‌بندی

دستورات PIVOT و UNPIVOT بخشی از امکانات پیشرفته Oracle SQL هستند که می‌توانند گزارش‌هایی بسیار شفاف، تجزیه‌پذیر و کاربرپسند ایجاد کنند.

چه بخواهید تحلیل فروش انجام دهید، چه بخواهید داده‌های خام را آماده‌سازی کنید، این ابزارها به شدت کارتان را راحت می‌کنند.

در کنار این قابلیت‌ها، ترکیب با توابع شرطی و Analytic Functions، قدرت فوق‌العاده‌ای به شما می‌دهد.

📥 اگر سوالی داری در مورد دستورات PIVOT و UNPIVOT در اوراکل داری، در بخش کامنت‌ها بپرس.

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *