SQL语句中把多条记录同一字段的值连接成一个字符串显示结果集(sql stuff)

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

结果：aijklmndf。

1.

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Inv] int,[order] varchar(4))
insert [tb]
select 10001,'P111' union all
select 10001,'P112' union all
select 10001,'P113' union all
select 10002,'P114' union all
select 10002,'P115'
--------------开始查询--------------------------
select inv, [order]=stuff((select ','+[order] from tb t where Inv=tb.Inv for xml path('')), 1, 1, '')
from tb
group by Inv

 

select id1, [id3]=stuff((select ','+id3 from id_t t where id1=id_t.id1 for xml path('')), 1, 1, '')
from id_t GROUP BY id1

 

2.

 

001	00101	20
001	00101	80
001	00101	75
002	00101	20
002	00101	10
003	00101	70
004	00101	70
004	00101	70
004	00101	70
id1	id2	id3

if object_id('f_str') is not null drop function f_str
go

CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ';' + id3
    FROM id_t
    WHERE id1=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id1, [order]=dbo.f_str(id1)
FROM id_t
GROUP BY id1