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??

Vadim Loboda · Accepted Answer · 2011-03-09 12:18:44Z

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

Collectives™ on Stack Overflow

Dynamic SQL Pivot by Column SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related