--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(banci varchar(8), name varchar(8), codename varchar(14)) insert into # select 'T90', '张三', '数学;语文;英语' union all select 'T90', '李四', '化学;语文;历史' union all select 'T91', '张三', '化学;语文' union all select 'T91', '王五', '英语';with a as ( select * from #, (select course='数学' union all select '语文' union all select '化学')c where charindex(';'+course+';', ';'+codename+';') > 0 ) select banci, 数学 = stuff((select ';'+name from a where course='数学' and banci=t.banci for xml path('')), 1, 1, ''), 数学 = stuff((select ';'+name from a where course='语文' and banci=t.banci for xml path('')), 1, 1, ''), 数学 = stuff((select ';'+name from a where course='化学' and banci=t.banci for xml path('')), 1, 1, '') from a as t group by banci/* banci 数学 语文 化学 T90 张三 张三;李四 李四 T91 张三 */
if object_id('tempdb.dbo.#') is not null drop table #
create table #(banci varchar(8), name varchar(8), codename varchar(14))
insert into #
select 'T90', '张三', '数学;语文;英语' union all
select 'T90', '李四', '化学;语文;历史' union all
select 'T91', '张三', '化学;语文' union all
select 'T91', '王五', '英语';with a as
(
select * from #, (select course='数学' union all select '语文' union all select '化学')c
where charindex(';'+course+';', ';'+codename+';') > 0
)
select
banci,
数学 = stuff((select ';'+name from a where course='数学' and banci=t.banci for xml path('')), 1, 1, ''),
数学 = stuff((select ';'+name from a where course='语文' and banci=t.banci for xml path('')), 1, 1, ''),
数学 = stuff((select ';'+name from a where course='化学' and banci=t.banci for xml path('')), 1, 1, '')
from a as t group by banci/*
banci 数学 语文 化学
T90 张三 张三;李四 李四
T91 张三
*/