34,590
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#tb') is null
drop table #tb
Go
Create table #tb([ID] int,[Type] nvarchar(21))
Insert #tb
select 1,N'A' union all
select 2,N'A' union all
select 3,N'A' union all
select 4,N'B' union all
select 5,N'B' union all
select 6,N'C'
Go
SELECT Type ,
STUFF(( SELECT ',' + RTRIM(ID)
FROM #tb
WHERE type = t.type
ORDER BY id
FOR
XML PATH('')
), 1, 1, '') rt
FROM (SELECT DISTINCT Type FROM #tb) AS t
;
/*
Type rt
A 1,2,3
B 4,5
C 6
*/
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#tb') is null
drop table #tb
Go
Create table #tb([ID] int,[Type] nvarchar(21))
Insert #tb
select 1,N'A' union all
select 2,N'A' union all
select 3,N'A' union all
select 4,N'B' union all
select 5,N'B' union all
select 6,N'C'
Go
SELECT Type ,
STUFF(( SELECT ',' + RTRIM(ID)
FROM #tb
WHERE type = t.type
ORDER BY id
FOR
XML PATH('')
), 1, 1, '') rt
FROM #tb AS t
GROUP BY Type;
/*
Type rt
A 1,2,3
B 4,5
C 6
*/
SELECT Type ,
STUFF(( SELECT ',' + RTRIM(ID)
FROM tb
WHERE type = t.type
ORDER BY id
FOR
XML PATH('')
), 1, 1, '') rt
FROM tb AS t
GROUP BY Type;