T-SQL sql拼接

  sql脚本也有拼接脚本的写法，sql拼接脚本如下：

declare @sql varchar(max)
declare @sqlCol varchar(max)
SELECT @sqlCol = ISNULL(@sqlCol + ',','') + ExamSubject FROM TB_Exam GROUP BY ExamSubject
set @sql = ' select * from '
set @sql = @sql + ' (select TB_Student.StuId,StuName,ExamScore,ExamSubject from TB_Exam '
set @sql = @sql + ' inner join TB_Student on TB_Exam.StuId = TB_Student.StuId) a '
set @sql = @sql + ' pivot '
set @sql = @sql + ' ( '
set @sql = @sql + ' max(ExamScore) for '
set @sql = @sql + ' a.ExamSubject in( '
set @sql = @sql + @sqlCol
set @sql = @sql + ' )'
set @sql = @sql + ' ) as b '
print (@sql)
exec (@sql)

建表脚本如下：

--学生信息表
create table TB_Student
(
	StuId int primary key,  --学生编号
	StuName varchar(50)     --学生姓名
);
insert into TB_Student(StuId,StuName) values(1,'刘备');
insert into TB_Student(StuId,StuName) values(2,'关羽');
insert into TB_Student(StuId,StuName) values(3,'张飞');
--考试成绩表
create table TB_Exam
(
	ExamId int primary key,  --考试编号
	StuId int,--学生编号
	ExamSubject varchar(50), --考试科目
	ExamScore int --考试成绩
);
insert into TB_Exam(ExamId,StuId,ExamSubject,ExamScore)
values(1,1,'语文',90);
insert into TB_Exam(ExamId,StuId,ExamSubject,ExamScore)
values(2,1,'数学',80);
insert into TB_Exam(ExamId,StuId,ExamSubject,ExamScore)
values(3,3,'语文',100);
select * from TB_Student;
select * from TB_Exam;