SQL Server - Query to retrieve column names and sum of columns

user_7788366 0
Viewed 718

Having difficulty with a query. I have a table called products with a large number of columns that looks like this:

 X  |  Y  |  Z  | ... (and on)
----+-----+-----+
 4  |  9  |  4  |
 5  |  2  |  6  |
 2  |  5  |  9  |

Then I want to develop a query that will output the column names and their sums like this:

ProductName  |  Quantity
-------------+-----------
    X        |     11
    Y        |     16
    Z        |     19
...

Any ideas?

Thanks in advance

tsql sql-server sql
3 Answers

Certainly UNPIVOT and perhaps Dynamic SQL would be more performant, but the following will "dynamically" unpivot and aggregate yourdata.

Please note that this converts the row to XML, and NULL values will be excluded

Example

Select ProductName = C.Field
      ,Quanity     = sum(C.Value)
 From  YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','int')           --<< Change to desired data type
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('FieldsTo','Exclude')
             ) C
 Group By C.Field

Returns

ProductName Quanity
X           11
Y           16
Z           19

If it helps with the Visualization the subquery generates:

enter image description here

user_15700000

Assuming your table is called yourtbl you can use UNPIVOT 

Select productName, SUM(Quantity) Quantity
from
(
select productName, Quantity
from
(
select * 
from yourtbl
)src
UNPIVOT
(
Quantity for ProductName in ([X],[Y],[Z])
)up
)t group by productName
user_11232260

You may use unpivot with SUM to achieve the expected result. To create SQL for such large number of attributes I would definitely use the dynamic T-SQL.

SELECT ProductName, Quantity
FROM (
   SELECT SUM(X) x, SUM(y) y, SUM(z) z FROM your_table
)
UNPIVOT
( 
   Quantity FOR ProductName IN (X, Y, Z)
) AS t
user_20912470
Related