تابع STRING AGG در SQL Server تجمیع قدرتمند رشته ها

تابع STRING_AGG در SQL Server: تجمیع قدرتمند رشته‌ها

تابع STRING_AGG در SQL Server یکی از توابع جدید معرفی‌شده در SQL Server 2017 است. این تابع مقادیر رشته‌ای را از یک گروه، به یک رشته واحد تجمیع (الحاق) می‌کند. این قابلیت برای تبدیل چندین ردیف به یک رشته تکی در پایگاه داده بسیار کاربردی است. پیش از SQL Server 2017، برای الحاق رشته‌ها در T-SQL نیاز به روش‌های جایگزین مانند FOR XML PATH یا STUFF بود که پیچیدگی‌های خاص خود را داشتند.

سینتکس کلی تابع STRING_AGG در SQL Server به این صورت است که شما یک عبارت (expression) و یک جداکننده (separator) را مشخص می‌کنید. عبارت، ستونی است که می‌خواهید مقادیر آن را تجمیع کنید و جداکننده، کاراکتر یا رشته‌ای است که بین هر مقدار قرار می‌گیرد:

STRING_AGG ( expression, separator ) [  ]

برای کنترل دقیق‌تر ترتیب الحاق رشته‌ها درون هر گروه، می‌توانید از عبارت WITHIN GROUP استفاده کنید که به شما امکان مرتب‌سازی صعودی یا نزولی را می‌دهد. این روش برای سازماندهی بهتر خروجی تجمیع رشته‌ها در SQL Server حیاتی است:

STRING_AGG ( expression, separator ) WITHIN GROUP ( ORDER BY  [ ASC | DESC ] )

در ادامه، به بررسی آرگومان‌های این تابع قدرتمند در SQL Server و نحوه کارکرد هر یک می‌پردازیم:

• expression (عبارت): می‌تواند عبارتی از هر نوع داده‌ای باشد. تابع STRING_AGG این عبارت را برای الحاق به نوع داده‌ای nvarchar یا varchar تبدیل می‌کند.
• separator (جداکننده): یک عبارت رشته‌ای از نوع nvarchar یا varchar است که بین مقادیر تجمیع‌شده قرار می‌گیرد. این جداکننده می‌تواند هر کاراکتر یا رشته‌ای باشد، مثلاً کاما، سیمی‌کالن یا یک فاصله.
• order_by_clause (بند مرتب‌سازی): یک بند اختیاری است که ترتیب منطقی الحاق رشته‌ها را مشخص می‌کند. این بخش برای زمانی که می‌خواهید رشته‌ها به ترتیب خاصی تجمیع شوند، بسیار مفید است.
• WITHIN GROUP (درون گروه): این کلمه کلیدی، ترتیب الحاق رشته‌ها را در داخل هر گروه تعریف می‌کند و کنترل دقیق‌تری بر خروجی نهایی فراهم می‌آورد.

برای درک بهتر نحوه کارکرد تابع STRING_AGG و کاربردهای آن در SQL Server، به مثال‌های عملی زیر توجه کنید. ابتدا، یک جدول ساده برای نمایش اطلاعات کارمندان ایجاد می‌کنیم. این جدول پایه‌ای برای تمام کوئری‌های STRING_AGG ما خواهد بود:

USE tempdb;
GO

CREATE TABLE EmployeeNames (
    EmployeeID INT IDENTITY(1,1),
    EmployeeName VARCHAR(50),
    Department VARCHAR(50)
);
GO

INSERT INTO EmployeeNames (EmployeeName, Department) VALUES
('John Doe', 'Sales'),
('Jane Smith', 'Marketing'),
('Peter Jones', 'Sales'),
('Alice Brown', 'HR'),
('Mike Davis', 'Marketing'),
('Sara White', 'Sales'),
('Chris Green', 'HR'),
('Emily Clark', 'Sales'),
('David Wilson', 'Marketing');
GO

مثال ساده STRING_AGG در SQL Server

در این مثال، تمام نام‌های کارمندان را با استفاده از تابع STRING_AGG و کاما به عنوان جداکننده، در یک رشته واحد تجمیع می‌کنیم. این کوئری SQL یک لیست یکپارچه از تمام نام‌ها ایجاد می‌کند که به راحتی قابل خوانش است:

SELECT STRING_AGG(EmployeeName, ', ') AS AllEmployeeNames
FROM EmployeeNames;
GO

استفاده از STRING_AGG با DISTINCT

اگر بخواهید تنها نام‌های منحصر به فرد (بدون تکرار) را تجمیع کنید، می‌توانید از کلمه کلیدی DISTINCT درون تابع STRING_AGG استفاده کنید. این قابلیت برای حذف داده‌های تکراری و ایجاد لیست‌های تمیزتر در پایگاه داده مفید است:

SELECT STRING_AGG(DISTINCT EmployeeName, ', ') AS DistinctEmployeeNames
FROM EmployeeNames;
GO

STRING_AGG با GROUP BY

تابع STRING_AGG اغلب با بند GROUP BY استفاده می‌شود تا رشته‌ها بر اساس یک ستون خاص گروه‌بندی شوند. در این مثال، نام کارمندان را بر اساس بخش (Department) گروه‌بندی می‌کنیم تا لیست کارمندان هر بخش را به صورت جداگانه داشته باشیم. این روش برای گزارش‌گیری و تحلیل داده‌ها کاربرد فراوانی دارد:

SELECT
    Department,
    STRING_AGG(EmployeeName, ', ') AS EmployeesInDepartment
FROM EmployeeNames
GROUP BY Department;
GO

STRING_AGG با ORDER BY

برای کنترل دقیق‌تر ترتیب رشته‌های تجمیع‌شده، می‌توانید از بند ORDER BY درون STRING_AGG استفاده کنید. این قابلیت تضمین می‌کند که نام‌ها در لیست نهایی به ترتیب خاصی (مثلاً الفبایی) قرار گیرند و سازماندهی داده‌ها را بهبود می‌بخشد:

SELECT STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY EmployeeName ASC) AS OrderedEmployeeNames
FROM EmployeeNames;
GO

همچنین می‌توانید این مرتب‌سازی را همراه با گروه‌بندی انجام دهید تا لیست‌های مرتب‌شده‌ای برای هر گروه داشته باشید. این کار باعث می‌شود خروجی‌های شما خواناتر و منظم‌تر باشند و درک اطلاعات را ساده‌تر می‌کند:

SELECT
    Department,
    STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY EmployeeName ASC) AS OrderedEmployeesInDepartment
FROM EmployeeNames
GROUP BY Department;
GO

STRING_AGG و مقادیر NULL

یکی از ویژگی‌های مهم تابع STRING_AGG این است که به طور پیش‌فرض، مقادیر NULL را در فرآیند تجمیع نادیده می‌گیرد. این رفتار باعث می‌شود لیست‌های شما شامل مقادیر خالی یا نامشخص نشوند. برای نمایش این رفتار، یک ردیف با نام NULL به جدول اضافه می‌کنیم:

INSERT INTO EmployeeNames (EmployeeName, Department) VALUES
(NULL, 'HR');
GO

SELECT STRING_AGG(EmployeeName, ', ') AS NamesWithNull
FROM EmployeeNames;
GO

همانطور که مشاهده می‌کنید، نام NULL در خروجی ظاهر نمی‌شود، که نشان‌دهنده نادیده گرفتن آن توسط تابع است. این به حفظ یکپارچگی و خوانایی داده‌ها کمک می‌کند.

STRING_AGG با جداکننده‌های مختلف

می‌توانید از جداکننده‌های مختلفی برای نمایش لیست‌های تجمیع‌شده استفاده کنید. این امکان به شما انعطاف‌پذیری بیشتری در فرمت‌بندی خروجی می‌دهد و می‌توانید خروجی را مطابق با نیازهای گزارش‌دهی خود تنظیم کنید:

SELECT STRING_AGG(EmployeeName, ' | ') AS PipedNames
FROM EmployeeNames;
GO

SELECT STRING_AGG(EmployeeName, '; ') AS SemicolonNames
FROM EmployeeNames;
GO

پاکسازی جدول

پس از اتمام کار با مثال‌ها و بررسی کاربردهای تابع STRING_AGG، می‌توانید جدول موقت ایجاد شده را از پایگاه داده حذف کنید:

DROP TABLE EmployeeNames;
GO

تابع STRING_AGG در SQL Server 2017 و نسخه‌های جدیدتر، و همچنین در Azure SQL Database، در دسترس است و ابزاری کارآمد برای مدیریت و تجمیع رشته‌ها در محیط‌های پایگاه داده مدرن محسوب می‌شود.