-- 示例数据 DECLARE @t TABLE(id int, value varchar(10)) 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' -- 创建视图 GO CREATE VIEW A AS 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, '') )NSELECT * FROM A /*--结果 id values ----------- ---------------- 1 aa,bb 2 aaa,bbb,ccc
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
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'
-- 创建视图
GO
CREATE VIEW A
AS
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, '')
)NSELECT * FROM A
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc