question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

How to add group by to dynamic query ?

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

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


How did you come to the idea of using dynamic queries instead of recursive CTE for performance reasons?


0 Votes 0 ·

yes
are this way faster or recursive faster

0 Votes 0 ·

You already have compared the methods and chosen the CTE query: https://docs.microsoft.com/en-us/answers/questions/417584/.



0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

I checked the variable output in your dynamic sql and found that it seems you should add:

 group by r1.familyid , r1.PortionKey, r2.PortionKey, r3.PortionKey, r4.PortionKey, r5.PortionKey, r6.PortionKey, r7.PortionKey, r8.PortionKey, m1.PortionKey, m2.PortionKey, m3.PortionKey, m4.PortionKey, m5.PortionKey, m6.PortionKey, m7.PortionKey, m8.PortionKey

Please try:

   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 @q6 varchar(max) = ''
   declare @q7 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 @q6 = concat(@q6, ', r', @i, '.PortionKey')
     set @q7 = concat(@q7, ', m', @i, '.PortionKey')
       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 ',@q6,@q7
       )
           
           
   exec (@q1)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.