What is the best practice to insert a row after some consecutive rows in a table?-CodePudding

I have the below data in a table:

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%

I want the above data to be like:

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
Total,Infotainment,Safe Exit Assist,Voice Commands,14,11,78.57%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
Total,Infotainment,Safe Exit Assist,Voice Recognition Tips,12,11,109.09%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
Total,Infotainment,Voice Recognition,Setting Clock,7,3,233.33%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Sound Settings,2,2,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%
Total,KIA Drive Wise,Android Auto,Driver Attention Warning,16,15,106.66%

I want to insert a row after some particular number of rows. Which loop in SQL server should I use to accomplish my work?

CodePudding user response：

You can use GROUPING SETS for this

SELECT
  REGION = CASE WHEN GROUPING(REGION) = 1 THEN 'Total' ELSE REGION END,
  CATEGORY,
  SUBCATEGORY,
  ItemName,
  ItemViews = SUM(ItemViews),
  sales = SUM(sales),
  ItemViewspercentage = FORMAT(SUM(ItemViews) * 1.0 / SUM(sales), 'P2')
FROM YourTable t
GROUP BY GROUPING SETS (
  (CATEGORY, SUBCATEGORY, ItemName, REGION),
  (CATEGORY, SUBCATEGORY, ItemName)
);

db<>fiddle