schustda schustda - 26 days ago 5
SQL Question

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

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

Answer Source

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