How to sum dynamic columns in SQL Server?

Question

Assume I have a table with 3 columns. Is there possible sum of each column without specifying name of the column?

And is there possible create a table with dynamic name of the column, and then sum of each column?

UPDATE: Here is my sample. First, I do a query and get the result like this:

---------
|  Col  |
---------
|  DLX  |
|  SUI  |
|  PRE  |
|  TWQ  |
---------

The number of row maybe different each time, and then I create a table with columns from rows above like this:

---------------------------------
|  DLX  |  SUI  |  PRE  |  TWQ  |
---------------------------------

And then I fill data the table from another table. After all, I sum each column. Because I will not know exactly name of the column, so I need sum of each column without specifying name of the column.

Answer 1

If your table is small (i.e. 10 columns) I would just do it manually. But if it's like 20+ columns, I would employ some dynamic sql.

To answer your question directly, yes, you can dynamically create a table with dynamic column names using dynamic sql.

Here's one way to do it: You can use INFORMATION_SCHEMA.COLUMNS View to get all the column names and put them in a temp table.

SELECT NAME INTO #COLUMNS 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = YourTable

Next, create a temp table to store your sums.

CREATE TABLE #SUMS (
COLUMN_NAME NVARCHAR(MAX),
SUM_COLUMN INT
)

You can then use dynamic sql and a loop to sum each column.

WHILE EXISTS(SELECT TOP 1 * FROM #COLUMNS)
    BEGIN
        DECLARE @COLUMN NVARCHAR(MAX) = (SELECT TOP 1 * FROM #COLUMNS)
        DECLARE @DYNAMICSQL NVARCHAR(MAX) = N'SELECT ' + @COLUMN + ' AS COLUMN_NAME, SUM(' + @COLUMN + ') FROM YourTable'

        INSERT INTO #SUMS
        EXEC SP_EXECUTESQL @DYNAMICSQL

        DELETE FROM #COLUMNS
        WHERE NAME = @COLUMN
    END

Then you would need another dynamic sql and loop to loop through the new table and create a temp table with the column names you want using the sum values and the table name you want.

You should be able to do that using the code already supplied above.