Use SQL to split and combine data in the database

Use SQL to split and combine data in the database

Using SQL to split and combine data in a database provides the following solutions:

Method 1:

With cte (
Select a. Id, A. [Uid], UserName FROM (
Select a. [id], REPLACE (Split. a. value ('.', 'nvarchar (max) '), ''', '') AS [Uid]
FROM
(
SELECT [id], CAST ('<M>' + REPLACE (Uid, '</M> <M> ') + '</M>' as xml) AS [Uid] FROM
) As a cross apply [Uid]. nodes ('/m') AS Split ()

) A inner join dbo. B on a. [Uid] = B. [Uid]
)
Select distinct classList = stuff (select ',' + UserName from cte B WHERE. id = B. id for xml path (''), 1, 1,'') FROM CTE

Method 2:

SELECT t. id, classList = STUFF ((
SELECT ',' + tt1.UserName
From a tt left join B tt1 ON ''+ tt. Uid + ', 'like' %' + tt1.Uid + ', %'
WHERE tt. id = t. id for xml path (''), 1, 1 ,'')
From a t
Order by t. id

Method 3:

Select id, (select UserName from B where Uid = t02.item) c into # ttt from dbo. A as t01 cross apply dbo. split (t01.Uid, ',') as t02

Select id, stuff (select ',' + c from dbo. # ttt as tb01 where tb01.id = tb02.id for xml path (''), 1, 1,'') as classList

From dbo. # ttt as tb02 group by id

Drop table # ttt

Method 3 requires a built-in method:

Create function split (
@ String varchar (255 ),
@ Separator varchar (255)
) Returns @ array table (item varchar (255 ))
As
Begin
Declare @ begin int, @ end int, @ item varchar (255)
Set @ begin = 1
Set @ end = charindex (@ separator, @ string, @ begin)
While (@ end <> 0)
Begin
Set @ item = substring (@ string, @ begin, @ end-@ begin)
Insert into @ array (item) values (@ item)
Set @ begin = @ end + 1
Set @ end = charindex (@ separator, @ string, @ begin)
End
Set @ item = substring (@ string, @ begin, len (@ string) + 1-@ begin)
If (len (@ item)> 0)
Insert into @ array (item) values (substring (@ string, @ begin, len (@ string) + 1-@ begin ))
Return
End

Let's take a look at the results below:

Table:

Table B:

Final result:

Based on the methods given in the discussion, I personally provide these three methods. If there are any mistakes, please kindly advise.

This article permanently updates the link address: