Dynamically sum table columns in SQL Server

Question

I have the following data in a table with columns Col1,Col2,Col3,Col4, Col5......... Col153

The sample data in the first four columns in the table:

CREATE TABLE dbo.MyTable (LocaID VARCHAR(10),Col1 INT, Col2 INT,Col3 INT,Col4 INT)
INSERT INTO dbo.MyTable values
('LV',2,6,4,7),('CH',4,8,3,1),('LV',1,3,9,3),('MC',7,0,5,4),
('LV',4,5,2,4),('MC',7,1,4,9),('MC',5,1,8,1),('CH',7,3,4,0),
('MC',2,5,7,3);

There are 153 columns that I need to sum individually. I used the query below to get the list of columns:

-- This is to get the columns names
SELECT 
COLUMN_NAME AS Col_Names
INTO #Column
From INFORMATION_SCHEMA.COLUMNS
Where  TABLE_NAME Like 'MyTable'

I tried to use the query below to dynamically sum them:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Col_Names) 
            FROM #Column
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query= 'SELECT
          SUM('+@cols+')
         FROM dbo.MyTable GROUP BY [LocaID]' 
PRINT @query

When I print the result to check if the query is correct I got this:

SELECT SUM([Col1],[Col2],[Col3],[Col4],[LocaID])
         FROM dbo.MyTable GROUP BY [LocaID]

Which is clearly not correct. What I need is:

SELECT
[LocaID],
SUM([Col1]) AS [Col1],
SUM([Col2]) AS [Col2],
SUM([Col3]) AS [Col3],
SUM([Col4]) AS [Col4]
FROM dbo.MyTable
GROUP BY [LocaID]

For all the 153 columns. There is only one LocaID in the table.

Answer 1

If you want to SUM individual column, you need to change your STUFF function. You need to apply SUM function for all the columns, like below.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',SUM(' + QUOTENAME(Col_Names) + ')'
            FROM #Column
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @query= 'SELECT [LocaID], '+@cols+' 
         FROM dbo.MyTable GROUP BY [LocaID]' 
PRINT @query

Answer 2

Thanks you so much @Vad Soft, I adjusted you query to give me what i want

DECLARE @cols AS NVARCHAR(MAX),
        @cols2 AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
SET @cols = STUFF((
----------------------------------------------------------------------------------
           SELECT DISTINCT CONCAT(',SUM(' + QUOTENAME(a.Col_Names) + ')  AS ',' ',b.RN)
            FROM #Column a
            LEFT JOIN 
            (
            SELECT
Col_Names AS RN

from  #Column
where Col_Names <> 'LocaID' ) b ON b.RN=a.Col_Names
-------------------------------------------------------------------------------------
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')  

SET @cols2 =REVERSE(RIGHT(REVERSE(@cols),LEN(REVERSE(@cols))- CHARINDEX(',',REVERSE(@cols)))) 
--PRINT @cols2
SET @query= 'SELECT [LocaID], 
        '+@cols2+'
         FROM dbo.MyTable GROUP BY [LocaID]' 
 --PRINT @cols
 EXEC (@query)
---------------------------
DROP TABLE #Column

Output

 print @query

SELECT    [LocaID], 
        SUM([Col1])  AS  Col1,
        SUM([Col2])  AS  Col2,
        SUM([Col3])  AS  Col3,
        SUM([Col4])  AS  Col4
        FROM dbo.MyTable GROUP BY [LocaID]