DECLARE @question table
(
 QuestionId int,
 Record uniqueidentifier,
 indexnumber int,
 questiondetail text,
  IsActive bit
 )
 -- select NEWID()
 insert into @question
 select 1,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',1,'serader',1 union all
 select 2,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',2,'serader1',1 union all
 select 3,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',3,'serader2',1 union all
 select 4,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',1,'rader1',1 union all
 select 5,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',2,'rader2',1 
 
 --select *from @question
  

 
 
 DECLARE @Choice table
(
 Choiceid int,
 QuestionId int,
 indexnumber int,
 choicedetail varchar(50),
 IsActive bit
 )
insert into @Choice
 select 1,1,1,'a',1 union all
 select 2,1,2,'b',1 union all
 select 3,1,3,'others',1 union all
 select 4,2,1,'rader1',1 union all
 select 5,2,2,'rader2',1 union all
 select 6,3,1,'a',1 union all
 select 7,4,1,'asertin',1 union all
 select 8,5,1,'ser123',1 
 
 DECLARE @Response table
(
 Response uniqueidentifier,
 Record uniqueidentifier,
 crdate  datetime,
 IsActive bit
  )
  insert into @Response
  select '943B4955-BF16-4DC8-869F-9907DFC95AF7','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',GETDATE(),1 union all
  select '72B15409-74B7-4185-A1DE-C3F2904E2787','CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',GETDATE(),1 union all
   select 'A70FC1C6-8ED5-4777-9535-D8FB18146D17','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',GETDATE(),1
  DECLARE @Responsechoice table
(
 Responsechoice int,
 Response uniqueidentifier,
 QuestionId int,
 Choiceid int, 
 IsActive bit
  )
   insert into @Responsechoice
  select 1,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,1,1 union all
  select 2,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,3,1 union all
  select 3,'943B4955-BF16-4DC8-869F-9907DFC95AF7',4,7,1 union all
  select 4,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',1,1,1

record	          QuestionId  questiondetail	indexnumber Choiceid	choicedetail	count	percentage
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323	1	serader	           1	         1	a	          2	100
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323	1	serader	            3	         3	other  	        1	  50
CBC0CFE1-3EE9-4444
-A6DA-C5B9554FF25D	4	rader1	            1	         7	asertin	        1	 100

;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

record	          QuestionId  questiondetail	indexnumber Choiceid	choicedetail	count	percentage
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323	1	serader	           1	         1	a	          2	100
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323	1	serader	            3	         3	other  	        1	  50
CBC0CFE1-3EE9-4444
-A6DA-C5B9554FF25D	4	rader1	            1	         7	asertin	        1	 100