Применение функции к именам динамических столбцов
Я хотел бы обработать результаты динамического свода, что приводит к переменным количествам столбцов данных с разными именами. Но они содержат данные, связанные друг с другом, и имеют один и тот же тип данных. Для каждого из столбцов результатов я хотел бы применить идентичную функцию ISNULL. Но так как я не знаю названий столбцов, запись операции столбец за столбцом невозможна.
Вот Скрипт SQL. И пример таблицы:
CREATE TABLE T (ID INT UNIQUE NOT NULL, C1 INT NULL, C2 INT NULL, C3 INT NULL);
INSERT INTO T VALUES
(0, NULL, NULL, NULL),
(1, 9, NULL, NULL),
(2, NULL, 8, NULL),
(3, NULL, NULL, 10),
(4, 12, 61, NULL),
(5, 36, NULL, 86),
(6, NULL, 77, 42),
(7, 11, 22, 33);
SELECT * FROM T;
ID | C1 | C2 | C3
----+------+------+-----
0 | NULL | NULL | NULL
1 | 9 | NULL | NULL
2 | NULL | 8 | NULL
3 | NULL | NULL | 10
4 | 12 | 61 | NULL
5 | 36 | NULL | 86
6 | NULL | 77 | 42
7 | 11 | 22 | 33
Затем для каждого из этих столбцов будет применяться ISNULL(CN, 0). Как это могло быть достигнуто? Если это имеет какое-либо значение, поскольку сводной запрос является динамическим, эта обработка будет выполняться внутри EXEC sp_executesql.
Тогда ожидаемый результат будет следующим:
ID | C1 | C2 | C3
----+------+------+-----
0 | 0 | 0 | 0
1 | 9 | 0 | 0
2 | 0 | 8 | 0
3 | 0 | 0 | 10
4 | 12 | 61 | 0
5 | 36 | 0 | 86
6 | 0 | 77 | 42
7 | 11 | 22 | 33
@ Феликс не понял вопроса! Вы хотите не писать ISNULL для каждого столбца, но это должно работать как ISNULL?
@Феликс понял!
Вам может потребоваться динамически сгенерировать запрос с ISNULL и выполнить этот запрос. Перейдите по ссылке Как мы можем использовать ISNULL для всех имен столбцов в SQL Server 2008?
@Felix Опубликованный ответ, пожалуйста, проверьте
Ответы 3
Вы можете сделать это с помощью INFORMATION_SCHEMA, STUFF и Dynamic SQL:
-- Get the all columns names from the underlying table
SELECT COLUMN_NAME
INTO #TEMP
FROM [Database_Name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'T' AND COLUMN_NAME != 'ID'
DECLARE @COLUMNS NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
-- Construct a string with ISNULL
SELECT @COLUMNS = STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(COLUMN_NAME) + ',0) ' + QUOTENAME(COLUMN_NAME)
FROM #TEMP
ORDER BY 1
FOR XML PATH('')), 1, 1, '')
-- and use Dynamic SQL
SELECT @sql = 'SELECT ID,'+ @COLUMNS +' FROM T'
EXEC sp_executesql @sql
Надеюсь, это удовлетворит ваше требование
CREATE TABLE #T (ID INT UNIQUE NOT NULL, C1 INT NULL, C2 INT NULL, C3 INT NULL);
INSERT INTO #T VALUES
(0, NULL, NULL, NULL),
(1, 9, NULL, NULL),
(2, NULL, 8, NULL),
(3, NULL, NULL, 10),
(4, 12, 61, NULL),
(5, 36, NULL, 86),
(6, NULL, 77, 42),
(7, 11, 22, 33);
--SELECT * FROM #T;
Declare @Main varchar(max)=''
--use INFORMATION_SCHEMA.COLUMNS for physical table
select @Main += ',isnull('+name+',0) as '+name
from (select name from tempdb.sys.columns where object_id =object_id('tempdb..#T')) as spt
set @Main= 'select '+stuff(@Main ,1,1,'') + ' from #T'
Exec(@Main)
Мне не очень нравятся решения STUFF. А так как сводной запрос уже был динамическим, то с помощью Raka и этого отвечать по теме я сгенерировал список столбцов динамически. Мне этот код легче понять.
Предполагая, что столбцы, сгенерированные PIVOT, находятся в @Columns TABLE (Column VARCHAR) или подобных:
DECLARE @Isnull NVARCHAR(MAX);
SELECT @Isnull = ISNULL(@Isnull + ', ', '') + 'ISNULL(' + QUOTENAME([Column]) + ', 0)'
FROM Columns
EXEC sp_executesql N'SELECT ' + @Isnull + 'FROM whatever PIVOT things'
Каков ваш ожидаемый результат?