Динамический столбец Pivot SQL-сервер
Не могли бы вы мне помочь? Я нашел следующий код здесь, в Stackoverflow, однако он возвращает нули, и я бы хотел, чтобы вместо этого он возвращал 0. Поскольку это динамические столбцы, я изо всех сил пытаюсь жестко запрограммировать функцию объединения или isnull. Код показан ниже. Обратите внимание, что я беру данные из таблицы расстояний.
DECLARE
@sql NVARCHAR(MAX) = '',
@columns NVARCHAR(MAX) = '';
SELECT
@columns += QUOTENAME(VehicleRegistration) + ','
FROM
(SELECT DISTINCT VehicleRegistration FROM [##Distance]) A
ORDER BY
VehicleRegistration;
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @sql = '
SELECT * FROM
(
SELECT
d.VehicleRegistration AS AreaName,
CONCAT([Month],'' '',[Year]) AS Date,
Coalesce(SUM(d.AreaCount),0) AreaCount
FROM ##Distance d
GROUP BY VehicleRegistration, [Month], year ) t
PIVOT(
SUM(AreaCount)
FOR AreaName IN ('+ @columns +')
) AS pivot_table;';
END
END
EXECUTE (@sql);
В целом вам следует избегать конструкций типа SELECT @i = @i + ..., поскольку они не всегда работают. Вместо этого вы можете использовать STRING_AGG и другие современные методы.
См. антишаблон-использование-рекурсивного-присвоения переменных. Не доверяйте старому коду.
Ответы 1
Вам нужно использовать отдельную агрегацию, чтобы получить столбцы с ISNULL в ней.
Обратите внимание, что @variable += является задокументированной плохой идеей, вместо него следует использовать STRING_AGG или FOR XML.
Также используйте значение date, а не строку, так как это может быть более эффективно.
DECLARE
@sql nvarchar(max),
@columns nvarchar(max),
@columnsIsnull nvarchar(max);
SELECT
@columns = STRING_AGG(QUOTENAME(VehicleRegistration), ',')
WITHIN GROUP (ORDER BY VehicleRegistration),
@columnsIsnull = STRING_AGG('ISNULL(' + QUOTENAME(VehicleRegistration) + ', 0) AS ' + QUOTENAME(VehicleRegistration), ',')
WITHIN GROUP (ORDER BY VehicleRegistration)
FROM (
SELECT DISTINCT
VehicleRegistration
FROM [##Distance]
) A;
SET @sql = '
SELECT
Date,
' + @columnsIsnull + '
FROM (
SELECT
d.VehicleRegistration AS AreaName,
DATEFROMPARTS(Year, [Month], 1) AS Date,
ISNULL(SUM(d.AreaCount), 0) AreaCount
FROM ##Distance d
GROUP BY
VehicleRegistration,
[Month],
year
) t
PIVOT(
SUM(AreaCount)
FOR AreaName IN (
'+ @columns +'
)
) AS pivot_table;';
PRINT @sql; -- your friend
EXEC sp_executesql @sql;
На это уже ответили пару раз. Создайте еще одну переменную столбца, которая является версией ISNULL(..., 0) вашей другой переменной, и вместо выполнения SELECT * вы добавляете этот столбец, а также дату и т. д.