/* 测试数据 WITH p(name,age,love) AS ( SELECT 'liming',20,'012' ) ,h(id,love) AS ( SELECT 0,'backetball' UNION ALL SELECT 1,'football' UNION ALL SELECT 2,'piano' UNION ALL SELECT 3,'tennis' ) */ SELECT name, age, Stuff((SELECT ','+love FROM h WHERE CHARINDEX(Convert(varchar(11),h.id), p.love) <> 0 FOR XML PATH('') ), 1,1,'') AS love FROM p name age love ------ ----------- ----------------------------------- liming 20 backetball,football,piano
with a(name,age,love) as (select 'liming',20,'012' union all select 'job',19,'567'), b(id,love) as (select 0,'basketball' union all select 1,'football' union all select 2,'piano' union all select 3,'tennis' union all select 4,'ping-pong' union all select 5,'hope' union all select 6,'son' union all select 7,'2010')
select a.name,a.age,stuff((select ','+b.love from b where charindex(CAST(b.id as varchar(50)),a.love)>0 for xml path('')),1,1,'') as love from a/** name age love love ------------------------------------------------ liming 20 012 basketball,football,piano job 19 567 hope,son,2010 ------------------------------------------------- **/
WITH p(name,age,love) AS (
SELECT 'liming',20,'012'
)
,h(id,love) AS (
SELECT 0,'backetball' UNION ALL
SELECT 1,'football' UNION ALL
SELECT 2,'piano' UNION ALL
SELECT 3,'tennis'
) */
SELECT name,
age,
Stuff((SELECT ','+love
FROM h
WHERE CHARINDEX(Convert(varchar(11),h.id),
p.love) <> 0
FOR XML PATH('')
),
1,1,'') AS love
FROM p
name age love
------ ----------- -----------------------------------
liming 20 backetball,football,piano
(select 'liming',20,'012'
union all
select 'job',19,'567'),
b(id,love) as
(select 0,'basketball' union all
select 1,'football' union all
select 2,'piano' union all
select 3,'tennis' union all
select 4,'ping-pong' union all
select 5,'hope' union all
select 6,'son' union all
select 7,'2010')
select a.name,a.age,stuff((select ','+b.love from b where charindex(CAST(b.id as varchar(50)),a.love)>0 for xml path('')),1,1,'') as love from a/**
name age love love
------------------------------------------------
liming 20 012 basketball,football,piano
job 19 567 hope,son,2010
-------------------------------------------------
**/