table 1sid week room
1 1 A
1 1 A
1 1 B
1 2 A
1 3 B
1 3 C
1 3 D
1 4 A
2 1 A
2 2 A*注意,该表有重复数据求结果
sid week room
1 1 A,B
1 2 A
1 3 B,C,D
1 4 A
2 1 A
2 2 A
1 1 A
1 1 A
1 1 B
1 2 A
1 3 B
1 3 C
1 3 D
1 4 A
2 1 A
2 2 A*注意,该表有重复数据求结果
sid week room
1 1 A,B
1 2 A
1 3 B,C,D
1 4 A
2 1 A
2 2 A
若要SQL服务器处理 搜索论坛
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
INSERT @t SELECT 1,1, 'aa,dd'
UNION ALL SELECT 1,2,'bb'
UNION ALL SELECT 2,1, 'aaa'
UNION ALL SELECT 2,2,'bbb'
UNION ALL SELECT 2,1, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
sid ,week
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT room FROM @t N
WHERE sid = A.sid
And week=A.week
FOR XML AUTO
), '<N room="', ','), '"/>', ''), 1, 1, '')
)N测试过了,你可以直接使用!
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')
select * from tb
go CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO -- 调用函数
select id, value = dbo.f_str(id) FROM tb GROUP BY id drop table tb
drop function dbo.f_str