--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([MJRTGcode] int,[matcode2] nvarchar(37),[matcode] nvarchar(26),[cv5] nvarchar(21),[cv5name] int)
Insert #T
select 429943,N'G00041-1800-003',N'G00041',null,null union all
select 429944,N'G00041-1800-003-G',N'G00041',N'G',429943 union all
select 429945,N'G00041-1800-003-M',N'G00041',N'M',429943
Go
--测试数据结束
SELECT MJRTGcode ,
STUFF(( SELECT ','+matcode2
FROM #T b
WHERE b.cv5name = a.MJRTGcode
FOR
XML PATH('')
), 1, 1, '') AS matcode2,
STUFF(( SELECT ','+matcode
FROM #T b
WHERE b.cv5name = a.MJRTGcode
FOR
XML PATH('')
), 1, 1, '') AS matcode,
STUFF(( SELECT ','+cv5
FROM #T b
WHERE b.cv5name = a.MJRTGcode
FOR
XML PATH('')
), 1, 1, '') AS cv5
FROM #T a
WHERE cv5name IS NULL