Error When Creating Pivot Table with Multiple Dynamic Columns

Question

I'm trying to following the example from this SO post but I am receiving the following error:

(82938 row(s) affected) Msg 1056, Level 15, State 1, Line 1 The number of elements in the select list exceeds the maximum allowed number of 4096 elements. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'x'.

Here is my SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Description) 
                    from (Select distinct CompanyID, vchCompanyName as CompanyName, vchPlanDescription as Description, Convert(INT,bitRequiredByMatrix) As Required from tblCompany C
inner join
(Select distinct ContractorID, SUM(decAuditScore) as Score from 
[tblTrainingStandardAudits]
where bitRequiredByMatrix = 1 and TrainingStandardID = 1
Group By ContractorID) CS on CS.ContractorID = C.CompanyID and C.bitActive = 1
Inner Join tblTrainingStandardAuditSummary SAS on SAS.ContractorID = C.CompanyID
Inner join(SELECT distinct [ContractorID],[bitRequiredByMatrix],TP.vchPlanDescription
  FROM [PECV4].[dbo].[tblTrainingStandardAudits] SA
  inner join tblTrainingPlans TP on TP.BitwiseMatrixID = SA.BitwiseMatrixID AND SA.TrainingStandardID = TP.TrainingStandardID where TP.TrainingStandardID=1) RQ on RQ.ContractorID = C.CompanyID)OPP
                    group by Description, CompanyID
                    order by CompanyID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, Description
                from (Select distinct CompanyID, vchCompanyName as CompanyName, vchPlanDescription as Description, Convert(INT,bitRequiredByMatrix) As Required from tblCompany C
inner join
(Select distinct ContractorID, SUM(decAuditScore) as Score from 
[tblTrainingStandardAudits]
where bitRequiredByMatrix = 1 and TrainingStandardID = 1
Group By ContractorID) CS on CS.ContractorID = C.CompanyID and C.bitActive = 1
Inner Join tblTrainingStandardAuditSummary SAS on SAS.ContractorID = C.CompanyID
Inner join(SELECT distinct [ContractorID],[bitRequiredByMatrix],TP.vchPlanDescription
  FROM [PECV4].[dbo].[tblTrainingStandardAudits] SA
  inner join tblTrainingPlans TP on TP.BitwiseMatrixID = SA.BitwiseMatrixID AND SA.TrainingStandardID = TP.TrainingStandardID where TP.TrainingStandardID=1) RQ on RQ.ContractorID = C.CompanyID)OPP
            ) x
            pivot 
            (
                max(Required)
                for Description in (' + @cols + N')
            ) p '

exec sp_executesql @query;

I have no clue as to what I am doing wrong. If I manually create the pivot table by labeling each column like below:

  pivot(max(Required) for Description in ([ASBESTOS MGMT / MAINT WORK],[BENZENE / CHEMICAL EXPOSURE],[FORKLIFT],[NOISE]))pvt

There are 2634 rows. There are 48 columns total so I'm not sure where the 4096 elements is coming from in the error. Any assistance is greatly appreciated!

`PRINT @cols` to check number of columns. You use `group by Description, CompanyID`, thus i think `Description` is duplicated so much. Perhaps `group by Description` is correct. — TriV, May 09 '17 at 16:05

score 0 · Accepted Answer · answered May 10 '17 at 14:13

There were just too many duplicates of the columns in the Select statement. The solution was to use a different select that returned a distinct list of the column names. Here is what worked:

    SET @cols = STUFF((SELECT DISTINCT',' + QUOTENAME(Description) 
from (SELECT vchPlanDescription as Description from 
tblTrainingPlans
where TrainingStandardID = 1) cols

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

Then the set query:

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, Description
                from (Select distinct CompanyID, vchCompanyName as CompanyName, vchPlanDescription as Description, Convert(INT,bitRequiredByMatrix) As Required from tblCompany C
inner join
(Select distinct ContractorID, SUM(decAuditScore) as Score from 
[tblTrainingStandardAudits]
where bitRequiredByMatrix = 1 and TrainingStandardID = 1
Group By ContractorID) CS on CS.ContractorID = C.CompanyID and C.bitActive = 1
Inner Join tblTrainingStandardAuditSummary SAS on SAS.ContractorID = C.CompanyID
Inner join(SELECT distinct [ContractorID],[bitRequiredByMatrix],TP.vchPlanDescription
  FROM [PECV4].[dbo].[tblTrainingStandardAudits] SA
  inner join tblTrainingPlans TP on TP.BitwiseMatrixID = SA.BitwiseMatrixID AND SA.TrainingStandardID = TP.TrainingStandardID where TP.TrainingStandardID=1) RQ on RQ.ContractorID = C.CompanyID)OPP
            ) x
            pivot 
            (
                max(Required)
                for Description in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Hope this helps someone else.

Error When Creating Pivot Table with Multiple Dynamic Columns

1 Answers1