Home SQL SERVER The number of elements in the select list exceeds the maximum allowed number of 4096 elements
Reply: 1

SQL SERVER The number of elements in the select list exceeds the maximum allowed number of 4096 elements

akhrot
1#
akhrot Published in 2017-11-13 12:45:02Z

SQL SERVER 2008

i have a data and i am trying to do pivot. while running the below code encountered with error as

The number of elements in the select list exceeds the maximum allowed number of 4096 elements.

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);

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.

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,''); 

is there any way to handle such scenario. Please suggest

Gordon Linoff
2#
Gordon Linoff Reply to 2017-11-13 12:49:28Z

No, there isn't. The maximum number of columns in a SELECT statement is 4,096 (see here).

You would have a column for each value in the IN list. Even if you fixed one problem, you would just face another.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.338174 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO