sql server 一对多的时候拼成字符串
SELECT
cn_name = (
stuff(
(
SELECT
';' + t.cn_name
FROM
(
select 1 as project_id,cn_name,en_name from crs_master_data where master_id in (select * from Fun_SplitStr('234,455',','))
) t
WHERE
t.project_id = a.project_id FOR XML path ('')
),
1,
1,
''
)
),
en_name = (
stuff(
(
SELECT
'; ' + t.en_name
FROM
(
select 1 as project_id,cn_name,en_name from crs_master_data where master_id in (select * from Fun_SplitStr('234,455',','))
) t
WHERE
t.project_id = a.project_id FOR XML path ('')
),
1,
1,
''
)
)
FROM
(
select 1 as project_id,cn_name,en_name from crs_master_data where master_id in (select * from Fun_SplitStr('234,455',','))
) a
group by project_id