34,594
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#cpkc') is null
drop table #cpkc
Go
Create table #cpkc([cpbh] nvarchar(28),[kwbh] nvarchar(27))
Insert #cpkc
select N'010103',N'B06-3-1' union all
select N'010101',N'B06-5-2' union all
select N'01010442',N'B06-5-2' union all
select N'010106',N'B06-8-1' union all
select N'010107',N'B06-5-2'
GO
if not object_id(N'Tempdb..#kwjbxx') is null
drop table #kwjbxx
Go
Create table #kwjbxx([kwbh] nvarchar(27),[cbbhs] NVARCHAR(100))
Insert #kwjbxx
select N'B06-5-2',null union all
select N'B06-8-1',null union all
select N'B06-3-1',null union all
select N'B06-5-2',null
Go
--测试数据结束
SELECT DISTINCT
kwbh ,
STUFF(( SELECT ',' + cpbh
FROM #cpkc
WHERE kwbh = #kwjbxx.kwbh
FOR
XML PATH('')
), 1, 1, '') AS cpbhs
FROM #kwjbxx
大神,我这个还是不太懂,如果说我直接使用的话需要改些什么呢。
--测试数据
if not object_id(N'Tempdb..#cpkc') is null
drop table #cpkc
Go
Create table #cpkc([cpbh] nvarchar(28),[kwbh] nvarchar(27))
Insert #cpkc
select N'010103',N'B06-3-1' union all
select N'010101',N'B06-5-2' union all
select N'01010442',N'B06-5-2' union all
select N'010106',N'B06-8-1' union all
select N'010107',N'B06-5-2'
GO
if not object_id(N'Tempdb..#kwjbxx') is null
drop table #kwjbxx
Go
Create table #kwjbxx([kwbh] nvarchar(27),[cbbhs] NVARCHAR(100))
Insert #kwjbxx
select N'B06-5-2',null union all
select N'B06-8-1',null union all
select N'B06-3-1',null union all
select N'B06-5-2',null
Go
--测试数据结束
SELECT DISTINCT
kwbh ,
STUFF(( SELECT ',' + cpbh
FROM #cpkc
WHERE kwbh = #kwjbxx.kwbh
FOR
XML PATH('')
), 1, 1, '') AS cpbhs
FROM #kwjbxx