英文:
T-SQL change column names in Pivot table
问题
我正在尝试重新命名这个PIVOT的列名。目前列名是Id,我想使用GlobalTagging表中的Title(而不是Id)。
我尝试将Title保存在一个变量中,然后将其用作别名。对于每个标题,它都给我一个错误 "无效的列名 'Abbinden'"。请帮忙!
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnId AS NVARCHAR(MAX)
DECLARE @ActualColumnName AS NVARCHAR(MAX)
SELECT @ColumnId = COALESCE(@ColumnId + ', ', '') + QUOTENAME(Id)
FROM (SELECT DISTINCT Id FROM GlobalTagging) AS Ids
SELECT @ActualColumnName = COALESCE(@ActualColumnName + ', ', '') + QUOTENAME(Title)
FROM (SELECT DISTINCT Title FROM GlobalTagging) AS Titles
SET @DynamicPivotQuery =
N'SELECT isnull(' + @ColumnId + ',0) AS ' + @ActualColumnName + '
FROM (
SELECT Video.Title, Video.Id AS Video_ID, Video.Id ,TI.GlobalTaggingId
FROM Video left outer join TaggedItems TI on TI.ItemId = Video.Id
) AS SourceTable
PIVOT (
Count(Id)
FOR GlobalTaggingId IN (' + @ColumnId + N')
) AS PivotTable';
EXEC sp_executesql @DynamicPivotQuery
英文:
I am trying to rename the column names of this PIVOT. As of right now, the column names are the Ids, I would like to have the Title (instead of the Id) from the GlobalTagging table.
I tried to save the title in a variable, then use it as the alias. For each title it's giving me an error "Invalid column name 'Abbinden'". Please help!
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnId AS NVARCHAR(MAX)
DECLARE @ActualColumnName AS NVARCHAR(MAX)
SELECT @ColumnId = COALESCE(@ColumnId + ', ', '') + QUOTENAME(Id)
FROM (SELECT DISTINCT Id FROM GlobalTagging) AS Ids
SELECT @ActualColumnName = COALESCE(@ActualColumnName + ', ', '') + QUOTENAME(Title)
FROM (SELECT DISTINCT Title FROM GlobalTagging) AS Titles
SET @DynamicPivotQuery =
N'SELECT isnull(' + @ColumnId + ',0) AS ' + @ActualColumnName + '
FROM (
SELECT Video.Title, Video.Id AS Video_ID, Video.Id ,TI.GlobalTaggingId
FROM Video left outer join TaggedItems TI on TI.ItemId = Video.Id
) AS SourceTable
PIVOT (
Count(Id)
FOR GlobalTaggingId IN (' + @ColumnId + N')
) AS PivotTable'
EXEC sp_executesql @DynamicPivotQuery
答案1
得分: 0
看起来你可以使用手动的PIVOT
查询来进行条件聚合。这可能是进行数据透视的最佳方式,因为它更加灵活。
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @Columns AS NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(
QUOTENAME(Title) + ' = COUNT(CASE WHEN GlobalTaggingId = ' + QUOTENAME(Id, '""') + ' THEN 1 END)',
', '
)
FROM (
SELECT DISTINCT Id, Title
FROM GlobalTagging
) AS Ids;
SET @DynamicPivotQuery =
N'
SELECT
v.Title,
' + @Columns + '
FROM Video v
LEFT JOIN TaggedItems TI ON TI.ItemId = v.Id
GROUP BY
v.Id,
v.Title;
';
EXEC sp_executesql @DynamicPivotQuery
英文:
It seems you can just use a manual PIVOT
query using conditional aggregation. This is probably the best way to do a pivot anyway as it's much more flexible.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @Columns AS NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(
QUOTENAME(Title) + ' = COUNT(CASE WHEN GlobalTaggingId = ' + QUOTENAME(Id, '''') + ' THEN 1 END)',
',
')
FROM (
SELECT DISTINCT Id, Title
FROM GlobalTagging
) AS Ids;
SET @DynamicPivotQuery =
N'
SELECT
v.Title,
' + @Columns + '
FROM Video v
LEFT JOIN TaggedItems TI ON TI.ItemId = v.Id
GROUP BY
v.Id,
v.Title;
'
EXEC sp_executesql @DynamicPivotQuery
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论