sum inside sa_rowgenerator?

[Breck_Carter]

How do I do this correctly? Want to use sum inside sa_rowgenerator.

CREATE TABLE t (  
   mat NVARCHAR(20),
   quantity INTEGER NOT NULL );

INSERT t VALUES ( 'ABC', 10 );
INSERT t VALUES ( 'ABC', 5 );
INSERT t VALUES ( 'ABC21', 7 );
INSERT t VALUES ( 'ABC21', 13 );
COMMIT;

SELECT NoOfRovs.row_num
FROM t CROSS APPLY sa_rowgenerator(1, sum(t.quantity)) NoOfRovs
WHERE mat = 'ABC';

[Breck_Carter]

Please explain what you are trying to accomplish. It is unlikely that "sum inside sa_rowgenerator" is a solution, but who knows? Not me 🙂

[Former Member]

Excuse my faintness. In my example, I want to summarize quantity on 'ABC' and create as many rows (numbered) as what the sum becomes. In this case, there should be 15 rows counted from 1 to 15.

[Former Member]

The result should be like this:

row_num
-------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[Breck_Carter]

The SUM has to be calculated in the SELECT FROM t and the result can then be passed to sa_rowgenerator via CROSS APPLY ( and congratulations on showing another cool use for CROSS APPLY 🙂

CREATE TABLE t (  
   mat NVARCHAR(20),
   quantity INTEGER NOT NULL );

INSERT t VALUES ( 'ABC', 10 );
INSERT t VALUES ( 'ABC', 5 );
INSERT t VALUES ( 'ABC21', 7 );
INSERT t VALUES ( 'ABC21', 13 );
COMMIT;

SELECT NoOfRovs.row_num
  FROM ( SELECT SUM ( t.quantity ) AS sum_quantity 
          FROM T 
         WHERE mat = 'ABC'
       ) AS sum_t
  CROSS APPLY sa_rowgenerator ( 1, sum_t.sum_quantity ) AS NoOfRovs;

    row_num 
----------- 
          1 
          2 
          3 
          4 
          5 
          6 
          7 
          8 
          9 
         10 
         11 
         12 
         13 
         14 
         15 
(15 rows)