I work on sql server 2012 I face issue I need to add group by to dynamic query
add group by based on r
group by r1.familyid,r1.portionkey ,r2.portionkey,r3.portionkey,r4.portionkey ,r5.portionkey ,r6.portionkey ,r7.portionkey ,r8.portionkey
this query below i need to add group by
declare @max_group_id int = ( select max(GroupID) from extractreports.dbo.GetFinalResultParts with(nolock) )
declare @q1 varchar(max) = ''
declare @q2 varchar(max) = ''
declare @q3 varchar(max) = ''
declare @q4 varchar(max) = ''
declare @q5 varchar(max) = ''
declare @i int = 1
while @i <= @max_group_id
begin
set @q1 = concat(@q1, ', nullif(r', @i, '.PortionKey, ''Blank'')')
set @q3 = concat(@q3, ', nullif(m', @i, '.PortionKey, ''Blank'')')
set @i += 1
end
set @i = 2
while @i <= @max_group_id
begin
set @q2 = concat(@q2, ' inner join extractreports.dbo.GetFinalResultParts r', @i, ' with(nolock) on r', @i, '.familyid = r1.familyid and r', @i, '.GroupID = ', @i)
set @q5 = concat(@q5, ' inner join extractreports.dbo.GetFinalResultMasks m', @i, ' with(nolock) on m', @i, '.familyid = r1.familyid and m', @i, '.GroupID = r',@i,'.GroupID')
set @i += 1
end
set @q1 = concat(
'select r1.familyid, concat(',
right(@q1, len(@q1) - 1),
') as PartNumber,concat(',
right(@q3, len(@q3) - 1),
') as MaskNumber' +' into extractreports.dbo.getfinalmask from extractreports.dbo.GetFinalResultParts r1 with(nolock) inner join extractreports.dbo.GetFinalResultMasks m1 with(nolock) on m1.familyid = r1.familyid and m1.GroupID = r1.GroupID and r1.GroupID =1 ',
@q2,@q5,+char(13) +
'group by r1.familyid,PartNumber,MaskNumber'
)
exec (@q1)
I need add group by r1.portionkey ,r2.portionkey,r3.portionkey,r4.portionkey ,r5.portionkey ,r6.portionkey ,r7.portionkey ,r8.portionkey
so how to do that