گزارش دهی پیشرفته با pivot و grouping sets در sqlserver

گزارش‌دهی پیشرفته با SQL PIVOT و GROUPING SETS در SQL Server

در برخی مواقع، کوئری‌های PIVOT ساده در SQL Server برای گزارش‌دهی پیشرفته کافی نیستند. این مقاله راهنمایی می‌کند که چگونه با ترکیب قابلیت PIVOT و GROUPING SETS، به گزارش‌های پیچیده‌تر و انعطاف‌پذیرتری دست یابید.

برای شروع، به داده‌های نمونه نیاز داریم. یک جدول موقت ایجاد کرده و آن را با داده‌های فروش پر می‌کنیم.

USE tempdb;
GO

IF OBJECT_ID('tempdb..#SalesData') IS NOT NULL
    DROP TABLE #SalesData;
GO

CREATE TABLE #SalesData (
    CompanyName VARCHAR(50),
    OrderYear INT,
    OrderMonth INT,
    SalesAmount DECIMAL(10, 2)
);
GO

INSERT INTO #SalesData (CompanyName, OrderYear, OrderMonth, SalesAmount) VALUES
('Company A', 2021, 1, 100.00),
('Company A', 2021, 2, 150.00),
('Company A', 2022, 1, 120.00),
('Company B', 2021, 1, 200.00),
('Company B', 2021, 3, 250.00),
('Company B', 2022, 2, 180.00),
('Company C', 2022, 1, 300.00),
('Company C', 2022, 3, 350.00);
GO

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

SELECT
    CompanyName,
    OrderYear,
    [1] AS Jan,
    [2] AS Feb,
    [3] AS Mar,
    [4] AS Apr,
    [5] AS May,
    [6] AS Jun,
    [7] AS Jul,
    [8] AS Aug,
    [9] AS Sep,
    [10] AS Oct,
    [11] AS Nov,
    [12] AS Dec
FROM
    (SELECT CompanyName, OrderYear, OrderMonth, SalesAmount FROM #SalesData) AS SourceTable
PIVOT
    (SUM(SalesAmount) FOR OrderMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
ORDER BY CompanyName, OrderYear;
GO

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

حالا فرض کنید می‌خواهیم مجموع فروش برای هر شرکت و همچنین یک مجموع کل (grand total) را اضافه کنیم. می‌توانیم این کار را با استفاده از GROUPING SETS (یا ROLLUP/CUBE) در کنار PIVOT انجام دهیم.

عبارت GROUPING SETS به ما امکان می‌دهد گزینه‌های گروه‌بندی چندگانه را در یک دستور SELECT تعریف کنیم. به عنوان مثال، برای دریافت مجموع کل بر اساس شرکت و یک مجموع کل نهایی، می‌توانیم به صورت زیر مشخص کنیم:

در اینجا، هر عنصر داخل پرانتز یک سطح از تجمیع را نشان می‌دهد:
* (CompanyName, OrderYear): سطرهای جزئی بر اساس شرکت و سال را ارائه می‌دهد.
* (CompanyName): مجموع فروش برای هر شرکت در تمام سال‌ها را محاسبه می‌کند.
* (): مجموع کل (grand total) تمام فروش‌ها را نشان می‌دهد.

SELECT
    CASE WHEN GROUPING(CompanyName) = 1 THEN 'Total Sales' ELSE CompanyName END AS CompanyName,
    CASE WHEN GROUPING(OrderYear) = 1 AND GROUPING(CompanyName) = 0 THEN 'Year Total'
         WHEN GROUPING(OrderYear) = 1 AND GROUPING(CompanyName) = 1 THEN ''
         ELSE CAST(OrderYear AS VARCHAR(4)) END AS OrderYear,
    ISNULL([1], 0) AS Jan,
    ISNULL([2], 0) AS Feb,
    ISNULL([3], 0) AS Mar,
    ISNULL([4], 0) AS Apr,
    ISNULL([5], 0) AS May,
    ISNULL([6], 0) AS Jun,
    ISNULL([7], 0) AS Jul,
    ISNULL([8], 0) AS Aug,
    ISNULL([9], 0) AS Sep,
    ISNULL([10], 0) AS Oct,
    ISNULL([11], 0) AS Nov,
    ISNULL([12], 0) AS Dec,
    SUM(ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) +
        ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) +
        ISNULL([11], 0) + ISNULL([12], 0)) AS YearlyTotal
FROM
    (SELECT CompanyName, OrderYear, OrderMonth, SalesAmount FROM #SalesData) AS SourceTable
PIVOT
    (SUM(SalesAmount) FOR OrderMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
GROUP BY GROUPING SETS
    (
        (CompanyName, OrderYear),
        (CompanyName),
        ()
    )
ORDER BY
    GROUPING(CompanyName) DESC,
    CompanyName,
    GROUPING(OrderYear) DESC,
    OrderYear;
GO

توجه کنید که چگونه از تابع **GROUPING** برای شناسایی سطرهای تجمیع‌شده و اختصاص برچسب‌های توصیفی مانند ‘Total Sales’ و ‘Year Total’ استفاده شده است. خروجی اکنون شامل مجموع فروش برای هر شرکت و یک مجموع کل نهایی در انتها است.

چه می‌شود اگر به مجموع فروش نه تنها بر اساس شرکت، بلکه بر اساس سال و سپس یک مجموع کل نهایی نیاز داشته باشیم؟ می‌توانیم GROUPING SETS خود را گسترش دهیم.

در این حالت، سطوح تجمیع شامل موارد زیر است:
* (CompanyName, OrderYear): سطرهای جزئی بر اساس شرکت و سال را فراهم می‌کند.
* (CompanyName): مجموع فروش برای هر شرکت را در تمام سال‌ها محاسبه می‌کند.
* (OrderYear): مجموع فروش برای هر سال را در تمام شرکت‌ها نشان می‌دهد.
* (): مجموع کل نهایی تمام فروش‌ها را ارائه می‌دهد.

SELECT
    CASE WHEN GROUPING(CompanyName) = 1 AND GROUPING(OrderYear) = 0 THEN 'Total Sales by Year'
         WHEN GROUPING(CompanyName) = 1 AND GROUPING(OrderYear) = 1 THEN 'Grand Total'
         ELSE CompanyName END AS CompanyName,
    CASE WHEN GROUPING(OrderYear) = 1 AND GROUPING(CompanyName) = 0 THEN 'Annual Total'
         WHEN GROUPING(OrderYear) = 1 AND GROUPING(CompanyName) = 1 THEN ''
         ELSE CAST(OrderYear AS VARCHAR(4)) END AS OrderYear,
    ISNULL([1], 0) AS Jan,
    ISNULL([2], 0) AS Feb,
    ISNULL([3], 0) AS Mar,
    ISNULL([4], 0) AS Apr,
    ISNULL([5], 0) AS May,
    ISNULL([6], 0) AS Jun,
    ISNULL([7], 0) AS Jul,
    ISNULL([8], 0) AS Aug,
    ISNULL([9], 0) AS Sep,
    ISNULL([10], 0) AS Oct,
    ISNULL([11], 0) AS Nov,
    ISNULL([12], 0) AS Dec,
    SUM(ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) +
        ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) +
        ISNULL([11], 0) + ISNULL([12], 0)) AS SubTotal
FROM
    (SELECT CompanyName, OrderYear, OrderMonth, SalesAmount FROM #SalesData) AS SourceTable
PIVOT
    (SUM(SalesAmount) FOR OrderMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
GROUP BY GROUPING SETS
    (
        (CompanyName, OrderYear),
        (CompanyName),
        (OrderYear),
        ()
    )
ORDER BY
    GROUPING(CompanyName) DESC,
    CompanyName,
    GROUPING(OrderYear) DESC,
    OrderYear;
GO

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