动态 SQL Pivot by Column SQL Server [英] Dynamic SQL Pivot by Column SQL Server

问题描述

假设我们有 n 列和 m 行

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

一般如何使用动态SQL来做

How to use dynamic SQL to do in general

输出(一个有n行的表，带有，自动增量ID，Table1的列名和列之和):

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

其中SUM(columnName) 是Table 1 的所有值的总和， 如何对任何大小的任何表执行此操作，其中n 可以是 50，换句话说，一个 50 列的表格??

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

推荐答案

它可能看起来像这样:

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

这篇关于动态 SQL Pivot by Column SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！