SQL query with SUM, displaying the return in rows

Question

So class I have a question that is consuming my insides. Someone would be happy to tell me how to return a total of records from a query, via SUM (SQL Server) function, displaying the total number of rows proportional to the number of records instead of the aggregated value that the function does?

Exemplifying by SUM: acquire the total amount of money (just taking it as an example), and for this amount I display it in lines / records, suppose it has a total of R $ 500.00, so it would have to display 500 lines (1,2,3,4... up to 500).

Answer 1

I will assume the existence of a table containing numerical values:

CREATE TABLE #Tabela
(
    Codigo        INT,
    Montante      FLOAT,
)

INSERT INTO #Tabela(Codigo, Montante)VALUES
(1, 4),
(1, 2),
(2, 6),
(2, 1),
(3, 3),
(4, 2)

The sum of the amounts can be obtained through the following query:

SELECT Codigo, SUM(Montante) Soma
FROM   #Tabela
GROUP BY Codigo

To generate the number of rows proportional to the sum value for each code, an alternative is the following:

;WITH TodasAsSomas AS
(
    SELECT Codigo, SUM(Montante) Soma, ROW_NUMBER() OVER (ORDER BY SUM(Montante) DESC) RN
    FROM   #Tabela
    GROUP BY Codigo
),
Linhas AS
(
    SELECT 1 numL
    UNION ALL
    SELECT numL + 1
    FROM Linhas
    WHERE numL <= (SELECT Soma FROM TodasAsSomas WHERE RN = 1) 
)
SELECT S.Codigo, S.Soma, L.numL
FROM   TodasAsSomas S
INNER JOIN Linhas L
    ON L.numL <= S.Soma
ORDER BY 1, 2, 3
OPTION (maxrecursion 0)

Upshot:

Codigo  Soma    numL
1       6       1
1       6       2
1       6       3
1       6       4
1       6       5
1       6       6
2       7       1
2       7       2
2       7       3
2       7       4
2       7       5
2       7       6
2       7       7
3       3       1
3       3       2
3       3       3
4       2       1
4       2       2

• Note that the sum may result in a decimal number; it may be necessary to round the result to get the desired number of lines;
• If the goal is to generate the number of rows that corresponds to the sum of all values (without grouping by a code/key), it is only necessary to remove the 'Code' column and the GROUP BY clause, for example:

;WITH SomaTotal AS
(
    SELECT SUM(Montante) Soma
    FROM   #Tabela
),
Linhas AS
(
    SELECT 1 numL
    UNION ALL
    SELECT numL + 1
    FROM Linhas
    WHERE numL <= (SELECT Soma FROM SomaTotal) 
)
SELECT S.Soma, L.numL
FROM   SomaTotal S
INNER JOIN Linhas L
    ON L.numL <= S.Soma
ORDER BY 1, 2
OPTION (maxrecursion 0)

Upshot:

Soma    numL
18      1
18      2
18      3
18      4
18      5
18      6
18      7
18      8
18      9
18      10
18      11
18      12
18      13
18      14
18      15
18      16
18      17
18      18