ساخت PIVOT داینامیک SQL Server تبدیل سطر به ستون

ساخت کوئری PIVOT داینامیک در SQL Server: تبدیل هوشمند سطر به ستون

در دنیای مدیریت پایگاه داده، اغلب نیاز داریم تا ساختار داده‌ها را تغییر دهیم تا تحلیل یا گزارش‌گیری آسان‌تر شود. یکی از این تغییرات پرکاربرد، تبدیل ردیف‌ها به ستون‌هاست که در SQL Server با استفاده از عملگر PIVOT انجام می‌شود. عملگر PIVOT به شما امکان می‌دهد تا مقادیر منحصربه‌فرد از یک ستون را به ستون‌های جدید تبدیل کرده و یک تابع تجمیعی (aggregate function) را روی سایر ستون‌ها اعمال کنید.

به عنوان مثال، فرض کنید جدولی دارید که فروش محصولات را در ماه‌های مختلف نشان می‌دهد و می‌خواهید فروش هر محصول را به تفکیک ماه در یک سطر مشاهده کنید. در اینجا PIVOT به کار می‌آید.

محدودیت PIVOT استاتیک

با این حال، PIVOT دارای یک محدودیت اساسی است: نام ستون‌های خروجی (آن‌هایی که از مقادیر منحصربه‌فرد یک ستون ساخته می‌شوند) باید به صورت ثابت و از پیش تعریف شده باشند. این به این معنی است که اگر مقادیر موجود در ستون PIVOT شما تغییر کند یا موارد جدیدی اضافه شود، کوئری شما باید به صورت دستی به‌روزرسانی شود. این موضوع مدیریت داده‌ها را در محیط‌های پویا که داده‌ها به طور مداوم تغییر می‌کنند، دشوار می‌سازد.

نیاز به PIVOT داینامیک در SQL Server

برای غلبه بر این چالش، به راهکاری نیاز داریم که بتواند ستون‌های PIVOT را به صورت پویا و بر اساس داده‌های موجود در زمان اجرای کوئری ایجاد کند. اینجاست که مفهوم “PIVOT داینامیک” مطرح می‌شود. با استفاده از T-SQL و SQL پویا (Dynamic SQL)، می‌توانیم کوئری PIVOT خود را به گونه‌ای بسازیم که بدون نیاز به تغییر دستی، با تغییر داده‌ها سازگار باشد.

ایجاد داده‌های آزمایشی برای PIVOT

برای درک بهتر و پیاده‌سازی کوئری PIVOT داینامیک، ابتدا یک جدول نمونه ایجاد کرده و تعدادی داده در آن وارد می‌کنیم:

CREATE TABLE Employee
(
    EmpId int identity(1,1),
    EmpName varchar(100),
    Salary int,
    [Month] varchar(20)
);

INSERT INTO Employee VALUES ('Mohan',2000,'January');
INSERT INTO Employee VALUES ('Mohan',3000,'February');
INSERT INTO Employee VALUES ('Mohan',4000,'March');
INSERT INTO Employee VALUES ('Mohan',4000,'April');
INSERT INTO Employee VALUES ('Mohan',2000,'May');

INSERT INTO Employee VALUES ('John',2000,'January');
INSERT INTO Employee VALUES ('John',3000,'February');
INSERT INTO Employee VALUES ('John',4000,'March');
INSERT INTO Employee VALUES ('John',4000,'April');
INSERT INTO Employee VALUES ('John',2000,'May');

INSERT INTO Employee VALUES ('Ram',2000,'January');
INSERT INTO Employee VALUES ('Ram',3000,'February');
INSERT INTO Employee VALUES ('Ram',4000,'March');
INSERT INTO Employee VALUES ('Ram',4000,'April');
INSERT INTO Employee VALUES ('Ram',2000,'May');

با اجرای دستور SELECT زیر، می‌توانید داده‌های جدول Employee را مشاهده کنید:

SELECT * FROM Employee;

پیاده‌سازی کوئری PIVOT داینامیک

اکنون هدف این است که حقوق هر کارمند را در ستون‌هایی به نام ماه‌ها مشاهده کنیم. ماه‌ها (January, February و غیره) باید به صورت پویا شناسایی شوند. برای رسیدن به این هدف، مراحل زیر را دنبال می‌کنیم:

1. استخراج لیست ستون‌های PIVOT به صورت پویا

اولین گام این است که تمام مقادیر منحصربه‌فرد ستونی که می‌خواهیم به ستون‌های جدید تبدیل شود (در اینجا ستون `[Month]`) را استخراج کرده و آن‌ها را به صورت یک رشته با کاما جدا شده (comma-separated list) آماده کنیم. این رشته سپس در بخش IN کوئری PIVOT استفاده خواهد شد. برای این کار از توابع `STUFF` و `FOR XML PATH` استفاده می‌کنیم که بهترین روش برای ایجاد لیست‌های پویا در SQL Server هستند.

DECLARE @ColumnList VARCHAR(MAX);
SELECT @ColumnList = STUFF((SELECT ',' + QUOTENAME([Month])
                            FROM Employee
                            GROUP BY [Month]
                            ORDER BY
                                CASE [Month]
                                    WHEN 'January' THEN 1
                                    WHEN 'February' THEN 2
                                    WHEN 'March' THEN 3
                                    WHEN 'April' THEN 4
                                    WHEN 'May' THEN 5
                                    WHEN 'June' THEN 6
                                    WHEN 'July' THEN 7
                                    WHEN 'August' THEN 8
                                    WHEN 'September' THEN 9
                                    WHEN 'October' THEN 10
                                    WHEN 'November' THEN 11
                                    WHEN 'December' THEN 12
                                END
                            FOR XML PATH('')), 1, 1, '');
PRINT @ColumnList;

در این قطعه کد، `QUOTENAME([Month])` برای اطمینان از قرار گرفتن نام ماه‌ها در براکت‌ها (مانند `[January]`) استفاده می‌شود تا از مشکلات مربوط به نام‌گذاری ستون‌ها جلوگیری شود. عبارت `ORDER BY CASE` نیز برای مرتب‌سازی ماه‌ها به ترتیب صحیح آن‌ها به کار رفته است. نتیجه `@ColumnList` به شکل `[January],[February],[March],[April],[May]` خواهد بود.

2. ساخت کوئری PIVOT داینامیک

حالا که لیست ستون‌ها را داریم، می‌توانیم رشته SQL کامل برای کوئری PIVOT را بسازیم. این کوئری شامل انتخاب ستون‌ها، استفاده از PIVOT و اعمال تابع تجمیعی SUM بر روی ستون Salary خواهد بود. ساختار عمومی عملگر PIVOT به شکل زیر است:

PIVOT ( AggregateFunction(ValueColumn) FOR PivotColumn IN ( [DistinctValue1], [DistinctValue2], … )) AS Alias

این ساختار نشان می‌دهد که چگونه یک تابع تجمیعی (مانند SUM یا COUNT) بر روی یک ستون (`ValueColumn`) اعمال می‌شود، در حالی که مقادیر منحصربه‌فرد یک ستون دیگر (`PivotColumn`) به عنوان نام ستون‌های جدید استفاده می‌شوند. در مورد ما، `AggregateFunction` همان `SUM(Salary)`، `ValueColumn` همان `Salary`، `PivotColumn` همان `[Month]` و `DistinctValue1`, `DistinctValue2` و… همان ماه‌های درون `@ColumnList` خواهند بود.

DECLARE @SQLQuery VARCHAR(MAX);
SET @SQLQuery = 'SELECT EmpName, ' + @ColumnList + '
                 FROM Employee
                 PIVOT (SUM(Salary) FOR [Month] IN (' + @ColumnList + ')) AS PivotTable';
PRINT @SQLQuery;

در اینجا، متغیر `@SQLQuery` شامل رشته‌ای از کوئری SQL کامل است که آماده اجرا می‌باشد. این کوئری از متغیر `@ColumnList` که لیست ماه‌های پویا را نگه می‌دارد، استفاده می‌کند.

3. اجرای کوئری PIVOT داینامیک

برای اجرای رشته SQL که در متغیر `@SQLQuery` ذخیره شده است، از دستور `sp_executesql` استفاده می‌کنیم. این روش امن‌ترین و کارآمدترین راه برای اجرای SQL پویا در SQL Server است.

EXECUTE sp_executesql @SQLQuery;

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

مثال: PIVOT داینامیک با تابع COUNT

می‌توانید به جای SUM از توابع تجمیعی دیگر مانند COUNT نیز استفاده کنید. فرض کنید می‌خواهید تعداد حضور هر کارمند در ماه‌های مختلف را ببینید. کافیست تابع تجمیعی را در کوئری PIVOT تغییر دهید:

DECLARE @CountColumnList VARCHAR(MAX);
SELECT @CountColumnList = STUFF((SELECT ',' + QUOTENAME([Month])
                                FROM Employee
                                GROUP BY [Month]
                                ORDER BY
                                    CASE [Month]
                                        WHEN 'January' THEN 1
                                        WHEN 'February' THEN 2
                                        WHEN 'March' THEN 3
                                        WHEN 'April' THEN 4
                                        WHEN 'May' THEN 5
                                        WHEN 'June' THEN 6
                                        WHEN 'July' THEN 7
                                        WHEN 'August' THEN 8
                                        WHEN 'September' THEN 9
                                        WHEN 'October' THEN 10
                                        WHEN 'November' THEN 11
                                        WHEN 'December' THEN 12
                                    END
                                FOR XML PATH('')), 1, 1, '');

DECLARE @CountSQLQuery VARCHAR(MAX);
SET @CountSQLQuery = 'SELECT EmpName, ' + @CountColumnList + '
                      FROM Employee
                      PIVOT (COUNT(EmpId) FOR [Month] IN (' + @CountColumnList + ')) AS PivotTable';
EXECUTE sp_executesql @CountSQLQuery;

در این مثال، `COUNT(EmpId)` به جای `SUM(Salary)` استفاده شده است تا تعداد ردیف‌ها برای هر کارمند در هر ماه شمارش شود. این انعطاف‌پذیری به شما اجازه می‌دهد تا گزارش‌های متنوعی را با استفاده از PIVOT داینامیک تهیه کنید و ساختار داده‌ها را برای اهداف مختلف تحلیل و مشاهده، بهینه سازید. پیاده‌سازی کوئری PIVOT داینامیک یک مهارت کلیدی برای هر متخصص SQL Server است که به دنبال راهکارهای بهینه و انعطاف‌پذیر برای گزارش‌گیری و تحلیل داده‌ها می‌گردد.