SQL function merging a field together

Time:2022-5-30

Recently, it is necessary to query all the fields in the associated table and recombine them into one field. At this time, the ordinary connection query can not meet the requirements, and SQL functions are required to complete it:


ALTER function dbo.getResCodesByOwnerId(@OwnerId INT)
returns nvarchar(2000)
as
begin
DECLARE @codes VARCHAR(2000)
SET @codes=''
select @codes=stuff((select ','+residence_code from crm_owner co left join crm_owner_residence cor on co.id=cor.owner_id where [email protected] for xml path('')),1,1,'')
return @codes
END

Take the data with id = 2 as the test result:

Select (database name) Getrescodesbyownerid (fr.owner_id) as room_ Code
from t1 fr left join t2 frd on fr.owner_id=frd.owner_id

Results:


1101010105,11GU002,1101010104

Supplement: SQL stuff function splicing string

Today, I saw an article about juxtaposition with. I also studied it. It’s still good

To this effect.


create table tb(idint, value varchar(10))
insert into tbvalues(1,'aa')
insert into tbvalues(1,'bb')
insert into tbvalues(2,'aaa')
insert into tbvalues(2,'bbb')
insert into tbvalues(2,'ccc')
go
 
/*     stuff(param1, startIndex, length, param2)

Note: delete length characters from StartIndex (in SQL, it starts from 1 instead of 0) in Param1, and then replace the deleted characters with param2*/


SELECT id,
           value = stuff
             ((SELECT   ',' + value
               FROM     tb AS t
               WHERE   t .id = tb.id FOR xml path('')), 1, 1, '')
FROM     tb
GROUP BY id

This will do.

Data collected

/* 
Title: merge one of the strings by a field (simple merge) 
Author: (after 18 years of wind and rain, snow lotus blossomed on the iceberg) 
Location: Shenzhen, Guangdong 
 
Description: merge the following data into the value field by ID field. 
id  value 
----- ------  
1   aa 
1   bb 
2   aaa 
2   bbb 
2   ccc 
Results required: 
id   value 
------ -----------  
1   aa,bb 
2   aaa,bbb,ccc 
Namely: group by ID, find the sum of value (string addition) 
*/ 
--1. SQL2000 can only be solved with user-defined functions  
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
 
create function dbo.f_str(@id varchar(10)) returns varchar(1000) 
as 
begin 
 declare @str varchar(1000) 
 select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id 
 return @str 
end 
go 
 
--Call function  
select id , value = dbo.f_str(id) from tb group by id 
 
drop function dbo.f_str 
drop table tb  
 
--2. Methods in SQL2005  
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go  
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '') 
from tb 
group by id  
drop table tb 
  
--3. Merge data using cursors  
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
Declare @t table (id int, value varchar (100)) -- define the result set table variable  
--Define cursors and merge  
declare my_cursor cursor local for 
select id , value from tb 
declare @id_old int , @id int , @value varchar(10) , @s varchar(100) 
open my_cursor 
fetch my_cursor into @id , @value 
select @id_old = @id , @s='' 
while @@FETCH_STATUS = 0 
begin 
  if @id = @id_old 
    select @s = @s + ',' + cast(@value as varchar) 
  else 
   begin 
    insert @t values(@id_old , stuff(@s,1,1,'')) 
    select @s = ',' + cast(@value as varchar) , @id_old = @id 
   end 
  fetch my_cursor into @id , @value 
END 
insert @t values(@id_old , stuff(@s,1,1,'')) 
close my_cursor 
deallocate my_cursor 
 
select * from @t 
drop table tb

The above is my personal experience. I hope I can give you a reference, and I hope you can support developeppaer. If there is any mistake or not fully considered, please don’t hesitate to comment.