SQLServer: What is the error with this stored procedure for creating dynamic pivot table?

Question

I created the following stored procedure to get a dynamic pivot table which is expandable with any number of employees in the first column and with any number of courses in the first row

DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders =
      COALESCE(
        @PivotColumnHeaders + ',[' + cast(CourseName as varchar) + ']',
        '[' + cast(CourseName as varchar)+ ']'
      )
    FROM employee, courses

    DECLARE @PivotTableSQL NVARCHAR(MAX)
    SET @PivotTableSQL = N'
      select
      *
    from
    (
    SELECT     dbo.employee.Name, dbo.employee.BadgeNo, dbo.employee.Division, dbo.employee.Organization, dbo.courses.CourseName, dbo.courses.CourseID
    FROM         dbo.employee LEFT OUTER JOIN
                          dbo.courses ON dbo.employee.CourseID = dbo.courses.CourseID
    ) DataTable
    PIVOT
    (
      Count(CourseID)
      FOR CourseName
      IN (
        '+ @PivotColumnHeaders+'
      )
    ) PivotTable
    '

    EXECUTE(@PivotTableSQL)

The problem with this stored procedure is: when I used sql server management studio tool to add courses and employee it gave me the following error:

Msg 8156, Level 16, State 1, Line 17 The column 'xxxxx' was specified multiple times for 'PivotTable'.

xxxxx is the name of the first employee in the table.

SPE109 SPE109 · Accepted Answer · 2011-10-17T07:27:53

Do you need to specifiy the distinct keyword on your first Select statement were you build up the column headers. I'm guessing that an employee an employee has maybe attended the same course twice or more (a refresher perhaps)

SQLServer: What is the error with this stored procedure for creating dynamic pivot table?

1 Answers