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

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

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

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

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

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 ('')

)

,''

) AS Result

) AS T

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

SELECT 
	',' + value
FROM @t AS t_in

SELECT

',' + value

FROM @t AS t_in

خروجی:

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

,II

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

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

SELECT

',' + value

FROM @t AS t_in

FOR XML PATH ('')

خروجی:

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

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

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

STUFF ( character_expression , start , length , replaceWith_expression )

1	STUFF ( character_expression , start , length , replaceWith_expression )

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

1	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

SELECT

STUFF(

(

SELECT

',' + value

FROM @t AS t_in

FOR XML PATH ('')

)

,''

) AS Result

خروجی:

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

1	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

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 ('')

)

,''

) 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

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

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

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', ',')

1	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

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)

برای مشاهده کاربردهای بیشتر این تابع کلیک کنید

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

دیدگاهتان را بنویسید لغو پاسخ

مطالب جدید

Building Custom Tasks for SQL Server Integration Services

نحوه اجرا دستورات در SSAS از طریق SQL Server

روش های پر کردن مقادیر NULL با استفاده از مقادیر موجود در رکوردهای قبلی

PRACTICAL MDX QUERIES

Multidimensional Expressions MDX Reference

دسته‌ها

بایگانی

آمار بازدید

درباره وبلاگ

نوشته‌های تازه

ابرچسب ها