Dynamic unpivot and summarize

Many times we need unpivot a table with unknown quantity and column names.

In this technique, I will show how do it using a code based on Cross Apply and XML.

Obvious it has a worse performance than SQL Server Unpivot operator but is helpful for a small set of data.

DECLARE @table TABLE (a INT, b INT,c INT ) 

INSERT @table (a, b, c) VALUES 
 ( 4, 9, 4)
,( 5, 2, 6)
,( 2, 5, 9)

SELECT ProductName = C.field 
       ,Quanity = Sum(C.value) 
FROM   @table 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') 
                    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  

The result of this code is: