SQL Server Dynamic SQL to calculate colums without knowing their number

l5tcr1uw 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(91)

I have a table that can have any number of fields starting with either aaa_ or bbb_ prefix - lets call them weight columns. And additionally I have a value column.

I would like to add calculated fields (number matches the number of weight colums) that multiplies all of the weight column values column by column with the value column.

For a fixed number of fields it would look like this:

SELECT [Id]
    ,[aaa_1]
    ,[bbb_1]
    ,[value]
    ,[aaa_1] * [value] AS [aaa_1_value]
    ,[bbb_1] * [value] AS [bbb_1_value]
FROM myTable

But as weights can be a large number and changing, I cannot do this in a static way.

I have managed to get the weight fields like this:

DECLARE @sql nvarchar(max)
SET @sql = 'SELECT [Id]
      ,[value] '
SELECT @sql = @sql + '[' + COLUMN_NAME +'],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'   
    AND (COLUMN_NAME LIKE 'aaa_%' OR COLUMN_NAME LIKE 'bbb_%')
SET @sql = @sql + ' 1 FROM myTable'
EXEC sp_executesql @sql

How can I add the multiplication dynamically?

sql-server

来源：https://stackoverflow.com/questions/75733726/dynamic-sql-to-calculate-colums-without-knowing-their-number

1条答案

按热度按时间

gcuhipw91#

You can just build it the same way you build the other columns

INFORMATION_SCHEMA.COLUMNS is for compatibility only. Use sys.columns instead.
Instead of concatenating [] , use QUOTENAME for correct quoting.
Do not use @var = @var + for aggregation, it has undefined behaviour. Use STRING_AGG instead.

DECLARE
  @sql nvarchar(max),
  @cols1 nvarchar(max),
  @cols2 nvarchar(max);

SELECT

  @cols1 = STRING_AGG(QUOTENAME(c.name), ',
  '),

  @cols2 = STRING_AGG(QUOTENAME(c.name) + ' * value AS ' + QUOTENAME(c.name + '_value'), ',
  ')

FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
WHERE t.name = 'myTable'   
  AND (c.name LIKE 'aaa_%' OR c.name LIKE 'bbb_%');

SET @sql = '
SELECT
  Id,
  ' + @cols1 + '
  value,
  ' + @cols2 + '
FROM myTable;
';

PRINT @sql;   -- your friend

EXEC sp_executesql @sql;

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Dynamic SQL to calculate colums without knowing their number

1条答案

相关问题

热门标签

最新问答