Dynamic SQL Pivot by Column SQL Server

Question

Supossing we have n columns with m rows

table 1:

someName1  someName2  someName3 ... someNameN
----------------------------------------------
12.5       12.34      56.6     ...    33.2
1.2323     12.5       57.2     ...    123.1
2.789      45.2       766.1    ...    56.2  
45.23      34.3       7.4      ...    33.4
52.1       4.3        89.8     ...    67.3

How to use dynamic SQL to do in general

Output (A table with n rows, with,autoincrement ID, column name of Table1 and Sum of column like):

ID    Column     Result
--------------------------------
1     someName1  SUM(someName1)=12.5+1.2323+2.789+45.23+52.1
2     someName2  SUM(someName2)=12.34+12.5+45.2+34.3+4.3
3     someName3  SUM(someName3)=56.6+57.2+766.1+7.4+89.8
...   ...
...   ...
N     someNameN  SUM(someName3)=33.2+123.1+56.2+33.4+67.3

Where SUM(columnName) is the value of summing all values of Table 1, How to do this for any size of any table, where n could be 50, in other words a table with 50 columns??

score 0 · Accepted Answer · answered Mar 09 '11 at 12:18

It might look like this:

declare @tableName nvarchar(128) = N'table_with_50_columns'
declare @columnLikeFilter  nvarchar(128) = N'someName%'

declare @columns nvarchar(2000) = '';
declare @sumColumns nvarchar(2000) = '';

select @columns = @columns + COLUMN_NAME + ',',
       @sumColumns = @sumColumns +  'sum(' + COLUMN_NAME + ') as ' + COLUMN_NAME + ','
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = @tableName and COLUMN_NAME like @columnLikeFilter
order by ORDINAL_POSITION ;

set @columns = left(@columns, len(@columns) - 1) ;
set @sumColumns = left(@sumColumns, len(@sumColumns) - 1) ;

declare @sql nvarchar(4000) =
    N';with cteColumnts (ORDINAL_POSITION, COLUMN_NAME) as 
    (
        select ORDINAL_POSITION, COLUMN_NAME 
        from INFORMATION_SCHEMA.COLUMNS 
        where TABLE_NAME = N'''+ @tableName + ''' and COLUMN_NAME like ''' + @columnLikeFilter + '''
    ),
    cteValues (ColumnName, SumValue) as
    (
        SELECT ColumnName, SumValue
        FROM 
           (SELECT ' + @sumColumns + '
           FROM dbo.' + @tableName + ') p
        UNPIVOT
           (SumValue FOR ColumnName IN 
              (' + @columns + ')
        )AS unpvt 
    )
    select row_number() over(order by ORDINAL_POSITION) as ID, ColumnName, SumValue
    from cteColumnts c inner join cteValues v on COLUMN_NAME = ColumnName
    order by ORDINAL_POSITION'

--print @sql

exec sp_executesql @sql

Dynamic SQL Pivot by Column SQL Server

1 Answers1

Linked