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

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

user1590
1#
user1590 Published in June 23, 2018, 9:19 pm

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

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO