22,209
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[name] nvarchar(21))
Insert #A
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([id] int,[pid] int,[psd] nvarchar(21),[www] NVARCHAR(100))
Insert #B
select 1,1,N'a',1111 union all
select 2,1,N'b',22222 union all
select 3,2,N'c',33333 union all
select 4,3,N'd',444444 union all
select 5,3,N'e',55555
GO
SELECT a.id,
a.name,
string_agg(psd, ',') AS psd,
string_agg(www, ',') AS www
FROM #A AS a
INNER JOIN #B AS b
ON a.id = b.pid
GROUP BY
a.id,
a.name
FOR JSON AUTO;
/*
[{"id":1,"name":"a","psd":"a,b","www":"1111,22222"},{"id":2,"name":"b","psd":"c","www":"33333"},{"id":3,"name":"c","psd":"d,e","www":"444444,55555"}]
*/
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[name] nvarchar(21))
Insert #A
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([id] int,[pid] int,[psd] nvarchar(21),[www] NVARCHAR(100))
Insert #B
select 1,1,N'a',1111 union all
select 2,1,N'b',22222 union all
select 3,2,N'c',33333 union all
select 4,3,N'd',444444 union all
select 5,3,N'e',55555
Go
--测试数据结束
SELECT id,a.name ,
STUFF(( SELECT ',' + psd
FROM #B
WHERE pid = a.id
FOR
XML PATH('')
), 1, 1, '') AS psd,
STUFF(( SELECT ',' + www
FROM #B
WHERE pid = a.id
FOR
XML PATH('')
), 1, 1, '') AS www
FROM #A a
GROUP BY a.id,a.name