What "percentage" does represent? How it supposed to be calculated?

The rest is here (you need to join just 3 of your tables):

;with agr_calc

as

(

select q.Record, q.QuestionId, q.indexnumber, rc.Choiceid, COUNT(*) [count]

from @question q

join @Responsechoice rc on rc.QuestionId = q.QuestionId and rc.IsActive = 1

join @Response r on r.Response = rc.Response and r.IsActive = 1 -- this join just to ensure that response is active

group by q.Record, q.QuestionId, q.indexnumber, rc.Choiceid

)

select a.Record, a.QuestionId, q.questiondetail, a.indexnumber, a.Choiceid, c.choicedetail, a.[count]

from agr_calc a

join @question q on q.QuestionId = a.QuestionId

join @choice c on c.Choiceid = a.Choiceid