sql用for xml将子表内容以字段的形式出现

代码

create table ClassDemo
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(50)
)
insert into ClassDemo(ClassName) values('一班');
insert into ClassDemo(ClassName) values('二班');
insert into ClassDemo(ClassName) values('三班');
select * from ClassDemo;

create table StudentDemo
(
	StudentID int primary key identity(1,1),
	StudentName nvarchar(50),
	ClassID int
)
insert into StudentDemo(StudentName,ClassID) values('张三',1);
insert into StudentDemo(StudentName,ClassID) values('李四',1);
insert into StudentDemo(StudentName,ClassID) values('王五',1);
insert into StudentDemo(StudentName,ClassID) values('马六',2);
insert into StudentDemo(StudentName,ClassID) values('曹七',2);

select * from StudentDemo;

select c.*,(select '{StudentID:'+cast(StudentID as nvarchar(50))+',StudentName:'+StudentName+'},' from StudentDemo where ClassID=c.ClassID for xml path('')) as StudentInfo from ClassDemo c

效果图