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 = 0
BEGIN
select @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 = 0
begin
select @totalCount = COUNT(*) from question_answer_selection (nolock) where optionid = @optionId;
select @questionOption = [option] from question_option where id = @optionId;
if @totalCount = 0
BEGIN
insert into @resultTable (question,question_option,
[大专],[本科],[硕士],[博士])
VALUES (@questionTitle,@questionOption,
0,0,0,0 );
fetch next from optionCursor into @optionId;
continue;
END
select @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.studentid
left join degree (nolock) on degree.id = survey_students.beginworkdegreeid
left join question_option (nolock) on question_option.id = question_answer_selection.optionid
group by degree.id,question_answer_selection.optionid,degree.degree
having question_answer_selection.optionid = @optionId) as t
pivot(
sum(ST) for [degree] in ([大专],[本科],[硕士],[博士])
) tbl
insert 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;
end
close optionCursor;
deallocate optionCursor;
fetch next from questionCursor into @questionId;
END
close questionCursor;
deallocate questionCursor;
select * from @resultTable;
T-SQL列转行
最新推荐文章于 2024-07-23 15:38:58 发布