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_idResults:
1101010105,11GU002,1101010104Supplement: 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 idThis 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 tbThe 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.