如何動態獲取表中多行的總和

我正在嘗試從資料庫中多個表的特定資料型別（金錢）的列中獲取總和。目前，我能夠從特定表中獲取列串列，但無法從這些列中獲取總和。

這就是我現在所擁有的

    use database 1 
Select   Column_Name
    From information_schema.columns
    Where TABLE_NAME = 'claimant'
    and data_type = 'money'

結果如下所示

我想要什么

@Squirrel 的更新這是我擁有的代碼，但它仍然給我帶來截斷問題。

{

declare @sql nvarchar(max);

select  @sql = 'with cte as ('   char(13)
              'select'   char(13)
              string_agg(char(9)   quotename(column_name)   ' = sum('   quotename(COLUMN_NAME)   ')', ','   char(13))   char(13)
              'from '   max(quotename(table_name))   char(13)
              ')'   char(13)
              'select a.table_name, a.column_name, a.total_sum '   char(13)
              'from   cte '   char(13)
              'cross apply ('   char(13)
              char(9)   'values'   char(13)
              string_agg(char(9)   '('''   table_name   ''','''   column_name    ''','   quotename(COLUMN_NAME)   ')', ','   char(13))   char(13)
              ') a (table_name, column_name, total_sum)'
from   information_schema.columns AS A
INNER JOIN EDL01.STAGING.TABLE_DETAILS B
ON A.TABLE_NAME = B.DEST_TABLE_NAME
where  A.table_name = B.DEST_TABLE_NAME
and    data_type  = 'money'



print @sql
exec sp_executesql @sql 
}

下面是創建表

CREATE TABLE [staging].[TABLE_DETAILS](
    [SOURCE_TABLE_NAME] [varchar](100) NULL,
    [DEST_TABLE_NAME] [varchar](100) NULL,
    [TYPE] [varchar](10) NULL,
    [PRIORITY] [int] NULL,
    [SOURCE_TABLE_DATABASE] [varchar](50) NULL,
    [SOURCE_TABLE_SCHEMA] [varchar](50) NULL,
    [DEST_TABLE_DATABASE] [varchar](50) NULL,
    [DEST_TABLE_SCHEMA] [varchar](50) NULL
) ON [PRIMARY]
GO

以下是部分結果

select a.table_name, a.column_name, a.total_sum 
from   cte 
cross apply (
    values
('PAYMENT','BILLEDAMOUNT',[BILLEDAMOUNT]),
    ('PAYMENT','AMOUNT',[AMOUNT]),
    ('SIMS_PAYMENT','CHECKAMOUNT',[CHECKAMOUNT]),
    ('BILLREVIEWHEADER','JURISDICTIONAMOUNT1',[JURISDICTIONAMOUNT1]),
    ('BILLREVIEWHEADER','JURISDICTIONAMOUNT2',[JURISDICTIONAMOUNT2]),
    ('BILLREVIE

uj5u.com熱心網友回復：

您需要動態形成查詢，然后使用sp_executesql或執行它exec()

注意：char(9)是制表符，char(13)是回車。添加這些以格式化查詢，以便在您print進行驗證時可以閱讀。

declare @sql nvarchar(max);

select @sql = 'with cte as ('   char(13)
              'select'   char(13)
              string_agg(char(9)   quotename(column_name)   ' = sum('   quotename(column_name)   ')', ','   char(13))   char(13)
              'from '   max(quotename(table_name))   char(13)
              ')'   char(13)
              'select a.table_name, a.column_name, a.total_sum '   char(13)
              'from   cte '   char(13)
              'cross apply ('   char(13)
              char(9)   'values'   char(13)
              string_agg(char(9)   '('''   table_name   ''', '''   column_name   ''','   quotename(column_name)   ')', ','   char(13))   char(13)
              ') a (table_name, column_name, total_sum)'
from   information_schema.columns
where  table_name = 'table_1'
and    data_type  = 'money'

print @sql
exec sp_executesql @sql

對于您的示例表，生成的動態查詢是

with cte as (
select
    [column_a] = sum([column_a]),
    [column_b] = sum([column_b]),
    [column_c] = sum([column_c])
from [table_1]
)
select a.table_name, a.column_name, a.total_sum 
from   cte 
cross apply (
    values
    ('table_1', 'column_a',[column_a]),
    ('table_1', 'column_b',[column_b]),
    ('table_1', 'column_c',[column_c])
) a (table_name, column_name, total_sum)

編輯使用回圈迭代每個表。基本上它對每個表執行上述查詢并將結果插入臨時表

參見db<>fiddle 演示

對于沒有 string_agg() 的早期 SQL Server 版本，請使用for xml path

select @sql  = 'with cte as ('   char(13)
               'select'   char(13)
               stuff
               (
                  (
                      select ','   quotename(COLUMN_NAME)   ' = sum('   quotename(COLUMN_NAME)   ')'
                      from   INFORMATION_SCHEMA.COLUMNS
                      where  TABLE_NAME = @table
                      and    DATA_TYPE  = 'money'
                      for xml path('')
                  ), 
                  1, 1, ''
               )   char(13)
              'from '   max(quotename(@table))   char(13)
              ')'   char(13)
              'select a.table_name, a.column_name, a.total_sum '   char(13)
              'from   cte '   char(13)
              'cross apply ('   char(13)
              char(9)   'values'   char(13)
              stuff
            (
              (
                  select    ','   '('''   TABLE_NAME   ''', '''   COLUMN_NAME   ''','   quotename(COLUMN_NAME)   ')'
                  from   INFORMATION_SCHEMA.COLUMNS
                  where  TABLE_NAME = @table
                  and    DATA_TYPE  = 'money'
                  for xml path('')
              ),
            1, 1, ''
            )
              ') a (table_name, column_name, total_sum)'   char(13)

標籤：sql 数据库 tsql 数据质量

上一篇：按計算列排序，在case運算式中具有別名

下一篇：AzureSynapseServerlessCETAS錯誤“外部表位置無效”