T-SQL change column names in Pivot table

Question

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

Answer 1

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