تبدیل مقادیر ستون به مقادیر جدا شده با کاما و برگردان سطر به سطر در SQL

عنوان عجیبیه ولی هر طور بخواهم تغییرش بدم به نظرم منظور رو نمی رسونه! برای همین خروجی که مد نظرمون هست رو در شکل زیر مشاهده می کنید. می خواهیم مقادیر این ستون ها رو با استفاده از کاما (ویرگول) از هم جدا کنیم و به صورت سطری برابر id مورد نظرشون قرار بدیم. هر چند که این کار با استفاده از زبان های برنامه نویسی چندان سخت نخواهد بود ولی در sql به چه صورت میشه این خروجی رو پیاده کرد هدف این پست خواهد بود. در ادامه به استفاده از دو روش پرکاربردتر و بهینه تر و البته ساده تر از مجموعه روش های موجود می پردازیم (پیشنهاد خودم روش دومه 😁). البته روش های دیگه نوشتن CLR یا Recursive CTE یا دستورهای while و cursor منظورمه! راستی با حضور افتخاریه STRING_SPLIT

و اما داده ها عبارت است از جدول زیر:

DECLARE @t TABLE (id INT NOT NULL, value varchar(20) NULL);
 
INSERT @t VALUES 
	(1, 'A'), 
	(1, 'B'),  
	(1, 'C'),  
	(2, 'X'), 
	(2, 'Y'),
    (2, 'Z'), 
	(3, 'I'), 
	(3, 'II');
 
SELECT * FROM @t

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

جواب:

SELECT DISTINCT
	T.id, T.Result
FROM 
	@t AS t_out
CROSS APPLY
	(
		SELECT 
			id,
			STUFF(
				(
					SELECT 
						',' + value
					FROM @t AS t_in
					WHERE t_in.id=t_out.id
					FOR XML PATH ('')
				)
				,1
				,1
				,''
			) AS Result
	) AS T

• اگر کوئری بالا رو بخواهیم قدم به قدم پیاده کنیم، قدم اول اضافه کردن کاما به همه مقادیر ستون value خواهد بود:

SELECT 
	',' + value
FROM @t AS t_in

خروجی:

,A
,B
,C
,X
,Y
,Z
,I
,II

• مرحله بعد تبدیل رکورد ها به یک مقدار هستش که با استفاده از XML PATH می تونیم اون رو تبدیل به یک عبارت XML کنیم.

SELECT 
	',' + value
FROM @t AS t_in
FOR XML PATH ('')

• نوبت حذف کاما اول هستش! که برای این کار از تابع STUFF استفاده می کنیم. این تابع چهار آرگومان به شکل زیر می گیره. آرگومان اول متن، ارگومان دوم شماره کاراکتر شروع در متن و آرگومان سوم طول کاراکترهای مورد نظر برای جایگزینی و آرگومان آخر هم متن جایگزینه. در مثال ما آرگومان اول یعنی متن، خروجی بالا خواهد بود. و از کاراکتر شماره 1 شروع می کنیم. و به همون اندازه 1 کاراکتر ادامه می دهیم. و با ” که متنی بدون هیچ کاراکتره جایگزین می کنیم. و این باعث حذف کاما اول خواهد شد.

STUFF ( character_expression , start , length , replaceWith_expression )

SELECT STUFF(',A,B,C,X,Y,Z,I,II', 1, 1, '')

SELECT 
	STUFF(
		(
			SELECT 
				',' + value
			FROM @t AS t_in
			FOR XML PATH ('')
		)
		,1
		,1
		,''
	) AS Result

خروجی:

A,B,C,X,Y,Z,I,II

• حالا با استفاده از CROSS APPLY خروجی بالا رو به id مورد نظر خودش اختصاص می دهیم.

SELECT 
	T.id, T.Result
FROM 
	@t AS t_out
CROSS APPLY
	(
		SELECT 
			id,
			STUFF(
				(
					SELECT 
						',' + value
					FROM @t AS t_in
					WHERE t_in.id=t_out.id
					FOR XML PATH ('')
				)
				,1
				,1
				,''
			) AS Result
	) AS T

خروجی:

• و اما در نهایت لازمه تکراری ها رو حذف کنیم. و کوئری نهایی به این صورت خواهد بود:

SELECT DISTINCT
	T.id, T.Result
FROM 
	@t AS t_out
CROSS APPLY
	(
		SELECT id, STUFF((SELECT ',' + value FROM @t AS t_in WHERE t_in.id=t_out.id FOR XML PATH ('')),1,1,'') AS Result
	) AS T

روش دوم: استفاده از STRING_AGG

روش دوم بسیار ساده تر و کوتاه تر از روش اوله. البته از این تابع به جای XML PATH استفاده می کنیم تا مقادیر رو به صورت Comma Separated بدست بیاریم.

جهت اطلاع: این تابع از SQL نسخه 2017 به بعد قابل استفاده و در دسترسه.

جواب:

SELECT id, STRING_AGG(value, ', ') AS Result 
FROM @t
GROUP BY id

خروجی:

و تنها نکته این که برای استفاده از STRING_AGG باید مقادیر دیگر در GROUP BY قرار بگیرند. این تابع هم مثل SUM و COUNT و مابقی توابع AGGREGATION عمل میکنه

معرفی STRING_SPLIT

بد نیست یک معرفی کوتاهی هم از تابع STRING_SPLIT داشته باشیم که دقیقا برعکس عمل STRING_AGG رو انجام میده فرض کنید داده های زیر رو داریم:

DECLARE @t TABLE (id INT NOT NULL, Result varchar(20) NULL);
 
INSERT @t VALUES 
	(1, 'A, B, C'), 
	(2, 'X, Y, Z'),  
	(3, 'I, II');
 
SELECT * FROM @t

برای استفاده از این تابع آن را در قسمت FROM قرار می دهیم به این صورت:

SELECT * FROM STRING_SPLIT('A, B, C', ',')

خروجی:

و اما جواب! برای گسترده کردن (expand) سطرها لازمه از CROSS APPLY استفاده کنیم:

SELECT tbl.id, T.value
FROM @t AS tbl
CROSS APPLY STRING_SPLIT(tbl.Result, ',') AS T

خروجی:

فقط دقت کنید value نام ستونی است که خود تابع ایجاد میکنه! و همچنین برای separator هم تنها از یک کارکتر میتونید استفاده کنید یعنی: nvarchar(1), varchar(1), nchar(1), char(1)