T-SQL在透视表中更改列名

huangapple go评论62阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年7月3日 16:40:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76603139.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定