Multiply Values from Col1 to Number of Columns in SQL

Question

If I have a dataset in SQL like this:

ColA    ColB    ColC     ColD     ColE
   4       8       9        1       10

How do I multiply each column value together but by indexing rather then typing out the column names such as:

1:number_of_columns(dataset)
Multiply all values together

The reason I want to index this is because the number of columns will not always be the same length and indexing this dataset would solve this problem in a dynamic fashion.

Final Output:

ColA    ColB    ColC     ColD     ColE      Total
   4       8       9        1       10      2,880

This final code though should be able to solve a dataset even like this:

ColE    ColG    ColD     
   45     33      44

Result:

ColE    ColG    ColD       Total  
   45     33      44      65,340

To dynamically change the columns or tables in a SQL statement you must use dynamically generated sql (SQL that is written by a script and then executed). [See here](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql) for how to do that with a Stored Procedure in Sql-Server. I question your schema though. It sounds like perhaps it's not the best set up for your needs. — JNevill, Sep 08 '17 at 19:07
@JNevill - *should* rather than *must* it is possible with a static query. — Martin Smith, Sep 08 '17 at 19:24

score 1 · Accepted Answer · answered Sep 08 '17 at 19:21

Your query should just look like

SELECT ColA,
       ColB,
       ColC,
       ColD,
       ColE,
       ColA * ColB * ColC * ColD * ColE AS product
FROM   T

And create it dynamically if necessary.

It is possible without dynamic SQL but not efficient or simple.

SELECT T.*, product
FROM   T
       CROSS APPLY (SELECT T.*
                    FOR XML PATH('X'), TYPE) CA(X)
       CROSS APPLY(SELECT CASE
                            --https://stackoverflow.com/a/5416205/73226
                            WHEN MIN(abs(n.value('.', 'int'))) = 0
                              THEN 0
                            ELSE EXP(SUM(Log(abs(NULLIF(n.value('.', 'int'), 0))))) -- the base mathematics
                                  * round(0.5 - count(NULLIF(sign(sign(n.value('.', 'int')) + 0.5), 1))%2, 0) -- pairs up negatives
                          END
                   FROM   X.nodes('/X/*') N(n)) ca2(product)

Multiply Values from Col1 to Number of Columns in SQL

1 Answers1