CODEWITHSUNDEEP
sql-server
Nick Knauer
Nick Knauer

Reputation: 4243

Multiply Values from Col1 to Number of Columns in SQL

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

Upvotes: 0

Views: 102

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

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)

Upvotes: 1

Related Questions