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 · Accepted Answer · 2011-10-17 07:27:53Z

-1

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)

answered Oct 17, 2011 at 7:27

SPE109

2,9511 gold badge19 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related