SQL Server Call function over every column sql

4bbkushb 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(145)

let's say I have a table with a lot of columns and I want to call same function on every of them (for example MAX ). Is it possible without listing all columns? That is instead of

Select max(col1), max(col2), max(col3)
from table1

write

Select max(col)
for col in table1.columns
from table1

sql-server

来源：https://stackoverflow.com/questions/75984722/call-function-over-every-column-sql

2条答案

按热度按时间

6xfqseft1#

There is no out of the box function to do that but since you have dynamic function tagged I assume that you want an example of dynamic code as well.

DECLARE @TableName VARCHAR(50) = 'put the table name here'
DECLARE @SqlCmd VARCHAR(MAX) = ''

SELECT @SqlCmd = @SqlCmd + ',MAX(' + QUOTENAME(c.name) + ') as ' + QUOTENAME(c.name)
        -- optional line break
        +CHAR(13)+CHAR(10)
FROM sys.columns c
INNER JOIN sys.tables t ON  c.object_id = t.object_id
WHERE t.name = @TableName
ORDER BY c.column_id

SET @SqlCmd = 'SELECT ' + STUFF(@SqlCmd,1,1,'') + ' FROM ' + QUOTENAME (@TableName)

--PRINT @SqlCmd
--SELECT @SqlCmd
EXEC (@SqlCmd)

赞(0）回复(0）举报 2023-06-21

5n0oy7gb2#

Execute the dynamic query using sp_executesql .

DECLARE @sqlQuery NVARCHAR(MAX) = N'SELECT';

SELECT @sqlQuery = @sqlQuery + ' MAX(' + QUOTENAME(COLUMN_NAME) + ') AS ' + QUOTENAME(COLUMN_NAME) + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1';

SET @sqlQuery = LEFT(@sqlQuery, LEN(@sqlQuery) - 1) + ' FROM table1';

EXEC sp_executesql @sqlQuery;

Hope it's work

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Call function over every column sql

2条答案

相关问题

热门标签

最新问答