SELECT 课程编号, 班级名称S=STUFF((SELECT ','+[班级名称] FROM table t WHERE 课程编号e=t1. 课程编号 FOR XML PATH('')), 1, 1, '')
FROM table t1
GROUP BY 课程编号
#3
select a.课程编号,
stuff((select ','+班级名称 from tb b
where b.课程编号=a.课程编号
for xml path('')),1,1,'') '班级名称'
from tb a
group by a.课程编号
#4
declare @galenkeny table(序号 int identity(1,1),课程编号 VARCHAR(10),班级名称 VARCHAR(10))
insert into @galenkeny
SELECT '010010', 'T1123-1' union all
select '010020', 'T1123-1' union all
select '010010', 'T1133-1' union all
SELECT '010020', 'T1153-1'
select b.课程编号,
班级名称=STUFF((SELECT ','+班级名称 FROM @galenkeny a WHERE b.课程编号=a.课程编号 FOR XML PATH('')),1,1,'')
FROM @galenkeny b
GROUP BY b.课程编号
declare @galenkeny table(序号 int identity(1,1),课程编号 VARCHAR(10),班级名称 VARCHAR(10))
insert into @galenkeny
SELECT '010010', 'T1123-1' union all
select '010020', 'T1153-1' union all
select '010010', 'T1133-1' union all
SELECT '010020', 'T1123-1'
select b.课程编号,
班级名称=STUFF((SELECT ','+班级名称 FROM @galenkeny a WHERE b.课程编号=a.课程编号 ORDER by 班级名称 FOR XML PATH('')),1,1,'')
FROM @galenkeny b
GROUP BY b.课程编号
#9
在FOR XML里面排序
SELECT 课程编号, 班级名称S=STUFF((SELECT ','+班级名称 FROM tb t WHERE 课程编号=t1.课程编号 ORDER BY 班级名称 FOR XML PATH('')), 1, 1, '')
FROM tb t1
GROUP BY 课程编号
insert into yx
select 1,'010010','T1123-1' union all
select 2,'010020','T1153-1' union all
select 3,'010010','T1133-1' union all
select 4,'010020','T1123-1'
select a.课程编号,
stuff((select ','+b.班级名称
from yx b
where b.课程编号=a.课程编号
order by b.班级名称
for xml path('')),1,1,'') '班级名称'
from yx a
group by a.课程编号
SELECT 课程编号, 班级名称S=STUFF((SELECT ','+[班级名称] FROM table t WHERE 课程编号e=t1. 课程编号 FOR XML PATH('')), 1, 1, '')
FROM table t1
GROUP BY 课程编号
#3
select a.课程编号,
stuff((select ','+班级名称 from tb b
where b.课程编号=a.课程编号
for xml path('')),1,1,'') '班级名称'
from tb a
group by a.课程编号
#4
declare @galenkeny table(序号 int identity(1,1),课程编号 VARCHAR(10),班级名称 VARCHAR(10))
insert into @galenkeny
SELECT '010010', 'T1123-1' union all
select '010020', 'T1123-1' union all
select '010010', 'T1133-1' union all
SELECT '010020', 'T1153-1'
select b.课程编号,
班级名称=STUFF((SELECT ','+班级名称 FROM @galenkeny a WHERE b.课程编号=a.课程编号 FOR XML PATH('')),1,1,'')
FROM @galenkeny b
GROUP BY b.课程编号
#5
select a.课程编号,
stuff((select ','+班级名称 from tb b
where b.课程编号=a.课程编号
for xml path('')),1,1,'') '班级名称'
from tb a
group by a.课程编号
mssql2005
SELECT 课程编号, 班级名称S=STUFF((SELECT ','+[班级名称] FROM table t WHERE 课程编号e=t1. 课程编号 FOR XML PATH('')), 1, 1, '')
FROM table t1
GROUP BY 课程编号
declare @galenkeny table(序号 int identity(1,1),课程编号 VARCHAR(10),班级名称 VARCHAR(10))
insert into @galenkeny
SELECT '010010', 'T1123-1' union all
select '010020', 'T1153-1' union all
select '010010', 'T1133-1' union all
SELECT '010020', 'T1123-1'
select b.课程编号,
班级名称=STUFF((SELECT ','+班级名称 FROM @galenkeny a WHERE b.课程编号=a.课程编号 ORDER by 班级名称 FOR XML PATH('')),1,1,'')
FROM @galenkeny b
GROUP BY b.课程编号
#9
在FOR XML里面排序
SELECT 课程编号, 班级名称S=STUFF((SELECT ','+班级名称 FROM tb t WHERE 课程编号=t1.课程编号 ORDER BY 班级名称 FOR XML PATH('')), 1, 1, '')
FROM tb t1
GROUP BY 课程编号
insert into yx
select 1,'010010','T1123-1' union all
select 2,'010020','T1153-1' union all
select 3,'010010','T1133-1' union all
select 4,'010020','T1123-1'
select a.课程编号,
stuff((select ','+b.班级名称
from yx b
where b.课程编号=a.课程编号
order by b.班级名称
for xml path('')),1,1,'') '班级名称'
from yx a
group by a.课程编号