تجمیع سطرها به ستون واحد SQLServer FOR XML PATH STRING AGG

تجمیع و ادغام هوشمندانه چندین سطر در یک ستون واحد در SQL Server

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

ابتدا، بیایید یک جدول و داده‌های نمونه ایجاد کنیم تا بتوانیم با آن‌ها کار کنیم:

CREATE TABLE dbo.Test (
    ColA INT,
    ColB VARCHAR(20)
);

INSERT INTO dbo.Test (ColA, ColB) VALUES
(1, 'First'),
(1, 'Second'),
(1, 'Third'),
(2, 'One'),
(2, 'Two');

SELECT * FROM dbo.Test;

این اسکریپت یک جدول ساده با دو ستون `ColA` و `ColB` ایجاد می‌کند و چند ردیف داده را در آن وارد می‌کند. خروجی `SELECT * FROM dbo.Test` به شکل زیر خواهد بود:

ColA | ColB
—–|———-
1 | First
1 | Second
1 | Third
2 | One
2 | Two

روش اول: استفاده از FOR XML PATH

در SQL Server 2005 و نسخه‌های بعدی، ما می‌توانیم از `FOR XML PATH` برای انجام این کار استفاده کنیم. بیایید ببینیم این روش چگونه کار می‌کند.

این هم کوئری مربوطه:

SELECT ColA,
STUFF((    SELECT ',' + ColB
FROM dbo.Test AS T2
WHERE T2.ColA = T1.ColA
FOR XML PATH('')
), 1, 1, '') AS CSV
FROM dbo.Test AS T1
GROUP BY ColA;

خروجی به شکل زیر خواهد بود:

ColA | CSV
—–|——————
1 | First,Second,Third
2 | One,Two

بیایید آنچه در اینجا اتفاق می‌افتد را بررسی کنیم. زیرکوئری (subquery) عمل الحاق (concatenation) را برای هر گروه `ColA` انجام می‌دهد. هر مقدار `ColB` بازگردانده شده و یک کاما قبل از آن قرار می‌گیرد. این یک لیست مانند “,First,Second,Third” ایجاد می‌کند. ما می‌خواهیم کامای اول را حذف کنیم، بنابراین از تابع `STUFF` برای جایگزینی اولین کاراکتر با یک رشته خالی استفاده می‌کنیم. تابع `STUFF` چهار آرگومان می‌گیرد:

STUFF ( character_expression , start , length , replaceWith_expression )

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

مدیریت مقادیر NULL

چه اتفاقی می‌افتد اگر مقادیر NULL وجود داشته باشند؟ بیایید چند NULL به داده‌هایمان اضافه کنیم:

INSERT INTO dbo.Test (ColA, ColB) VALUES
(3, 'Value1'),
(3, NULL),
(3, 'Value3');

اگر همان کوئری قبلی را اجرا کنیم، خروجی به شکل زیر خواهد بود:

ColA | CSV
—–|——————
1 | First,Second,Third
2 | One,Two
3 | Value1,,Value3

همانطور که می‌بینید، مقدار NULL برای `ColB` یک کامای اضافی ایجاد می‌کند. بنابراین، بیایید کوئری را برای مدیریت NULLها اصلاح کنیم:

SELECT ColA,
STUFF((    SELECT ',' + ColB
FROM dbo.Test AS T2
WHERE T2.ColA = T1.ColA AND T2.ColB IS NOT NULL
FOR XML PATH('')
), 1, 1, '') AS CSV
FROM dbo.Test AS T1
GROUP BY ColA;

خروجی به شکل زیر خواهد بود:

ColA | CSV
—–|——————
1 | First,Second,Third
2 | One,Two
3 | Value1,Value3

اکنون مقادیر NULL در رشته CSV لحاظ نمی‌شوند.

روش دوم: استفاده از STRING_AGG (فقط SQL Server 2017 و بالاتر)

در SQL Server 2017 و نسخه‌های بعدی، ما یک تابع داخلی جدید به نام `STRING_AGG` برای انجام این کار داریم. بیایید ببینیم این تابع چگونه کار می‌کند.

این هم کوئری مربوطه:

SELECT ColA, STRING_AGG(ColB, ',') AS CSV
FROM dbo.Test
GROUP BY ColA;

خروجی به شکل زیر خواهد بود:

ColA | CSV
—–|——————
1 | First,Second,Third
2 | One,Two
3 | Value1,,Value3

همانطور که می‌بینید، این روش بسیار ساده‌تر است. تابع `STRING_AGG` دو آرگومان می‌گیرد:

STRING_AGG ( expression, separator )

بنابراین، در مثال ما، `expression` همان `ColB` و `separator` همان کاما (`,`) است. به طور پیش‌فرض، `STRING_AGG` مقادیر NULL را نادیده می‌گیرد، اما به نظر می‌رسد از آنجایی که ما یک مقدار NULL برای `ColB` داریم، در واقع آن را به عنوان یک رشته استفاده کرده و نمایش می‌دهد (که منجر به یک کامای اضافی می‌شود). اگر به مستندات نگاه کنیم، آمده است: “مقادیر NULL نادیده گرفته می‌شوند. برای گنجاندن مقادیر NULL، از تابع `ISNULL ( expression, replacement )` استفاده کنید.” بنابراین، اگر می‌خواهید مقادیر NULL نادیده گرفته شوند، همچنان باید آن‌ها را به صراحت با استفاده از یک عبارت `WHERE` (مانند آنچه در بخش `FOR XML PATH` نشان داده شد) فیلتر کنید یا `ISNULL` را در اطراف `expression` خود قرار دهید.