T-SQL列转行

declare @resultTable table(question nvarchar(500),question_option nvarchar(500),[大专] float,[本科] float,[硕士] float,[博士] float);declare @questionId bigint;declare @optionId bigint;declare @doctorCount int,@masterCount int,@bachelarCount int, @specialCount int;declare @questionTitle nvarchar(500),@questionOption nvarchar(500);declare @totalCount float;declare questionCursor cursor local for select  id from question where paperId = 7;open questionCursor;fetch next from questionCursor into @questionId;while @@FETCH_STATUS = 0BEGINselect @questionTitle = question from question where id = @questionId;declare optionCursor cursor local for select id from question_option where questionId = @questionId;open optionCursor;fetch next from optionCursor into @optionId;while @@FETCH_STATUS = 0beginselect @totalCount = COUNT(*) from question_answer_selection (nolock) where optionid = @optionId;select @questionOption = [option] from question_option where id = @optionId;if @totalCount = 0BEGINinsert into @resultTable (question,question_option,[大专],[本科],[硕士],[博士]) VALUES (@questionTitle,@questionOption, 0,0,0,0);fetch next from optionCursor into @optionId;continue;ENDselect @specialCount = [大专], @bachelarCount = [本科], @masterCount = [硕士], @doctorCount = [博士]from (select degree.degree,COUNT(degree.id) as ST from question_answer_selection (nolock)left join survey_students (nolock) on survey_students.id = question_answer_selection.studentidleft join degree (nolock) on degree.id = survey_students.beginworkdegreeidleft join question_option (nolock) on question_option.id = question_answer_selection.optionidgroup by degree.id,question_answer_selection.optionid,degree.degreehaving question_answer_selection.optionid = @optionId) as tpivot(sum(ST)  for [degree] in ([大专],[本科],[硕士],[博士]) ) tblinsert into @resultTable (question,question_option,[大专],[本科],[硕士],[博士]) VALUES (@questionTitle,@questionOption, CONVERT(numeric(5,2),@specialCount/@totalCount,0),CONVERT(numeric(5,2),@bachelarCount/@totalCount),CONVERT(numeric(5,2),@masterCount/@totalCount),CONVERT(numeric(5,2),@doctorCount/@totalCount));fetch next from optionCursor into @optionId;endclose optionCursor;deallocate optionCursor;fetch next from questionCursor into @questionId;ENDclose questionCursor;deallocate questionCursor;select * from @resultTable;