title: SQL SERVER The number of elements in the select list exceeds the maximum allowed number of 4096 elements
body: <p>SQL SERVER 2008</p>
  
  <p>i have a data and i am trying to do pivot.
  while running the below code encountered with error as</p>
  
  <pre><code>The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>DECLARE @Cols NVARCHAR(MAX);
  DECLARE @SQL NVARCHAR(MAX);
  DECLARE @Sel NVARCHAR(MAX);
  DECLARE @GENERIC NVARCHAR(MAX)
  
  
  
  
  
  SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME([Description]) 
              FROM STAGING_TEMP 
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'');
  SET @Sel = STUFF((SELECT distinct ',' + QUOTENAME([Description]) + 
  ' AS DESC'+CAST(row_number () over (order by ([Description])) as varchar(250))
              FROM STAGING_TEMP 
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,''); 
  
  
  
  SET @SQL = N'
  SELECT DISTINCT [Generic Name], '+ @Sel+N'
  FROM STAGING_TEMP
      pivot
      (
        MAX(Description) For Description IN ('+ @Cols + N')
  
      ) P';
  
  EXECUTE( @SQL);
  </code></pre>
  
  <p>When i run the individual segment of code i came to know the below code produce 40000 desc value which has 35k Duplicate value AS Different DESC.</p>
  
  <pre><code>SET @Sel = STUFF((SELECT distinct ',' + QUOTENAME([Description]) + 
  ' AS DESC'+CAST(row_number () over (order by ([Description])) as varchar(250))
              FROM STAGING_TEMP 
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,''); 
  </code></pre>
  
  <p>is there any way to handle such scenario. Please suggest</p>