Just found the issue with this. The example dynamic query is all over the place, but here is what is missing:

In the example, using the code you posted, the problem is the N'SELECT statement contains the @ColumnName variable. Don't use that or the Pivot will group by it. You shouldn't need a group by clause at all as the Pivot operator does this for you.

Instead, use the original column that the Column names are derived from with an outer select.

In other words, instead of this:

SET @DynamicPivotQuery =

N'SELECT dayworked,' + @ColumnName + '

FROM tbldaysworked

PIVOT(max(fullday)

FOR name IN (' + @ColumnName + ')) AS PVTTable group by dayworked,' + @columnname + 'order by dayworked'

Use this:

SET @DynamicPivotQuery =

N'SELECT * FROM

(SELECT dayworked, name, fullday

FROM tbldaysworked) AS DW --just to give this inner query an alias

PIVOT(max(fullday)

FOR name IN (' + @ColumnName + ')) AS PVTTable)'

By doing this, the only column left to group by is the dayworked column, which is what you want.