Stuff Column Value handling select list exceeds the maximum allowed number of 4096 elements

Question

SQL SERVER 2012

DECLARE @TABLE TABLE
    (
    BASENAME varchar(150),
    DESCRIPTIONS varchar(150)
    )

    insert @TABLE

    SELECT 'Accessory', 'Color' UNION ALL
    SELECT 'Accessory', 'Compatibility'UNION ALL
    SELECT 'Accessory', 'Finish'UNION ALL
    SELECT 'Accessory', 'Material'UNION ALL
    SELECT 'Accessory', 'Mount Type'UNION ALL
    SELECT 'Adapter',   'Adjustable'UNION ALL
    SELECT 'Adapter',   'Ampere'UNION ALL
    SELECT 'Adapter',   'Compatibility'UNION ALL
    SELECT 'Adapter',   'MountType'UNION ALL
    SELECT 'Adapter',   'Operation Type'UNION ALL
    SELECT 'Adapter',   'Vertical Lift'UNION ALL
    SELECT 'Adapter',   'Drawbar'UNION ALL
    SELECT 'Adapter',   'Switch'

OUTPUT

BASENAME    DESC1   DESC2   DESC3   DESC4   DESC5   DESC6   DESC7   DESC8
Accessory   Color   Compatibility   Material    Mount Type  NULL    NULL    NULL    NULL
Adapter Adjustable  Ampere  Compatibility   MountType   Operation Type  Vertical Lift   Drawbar Switch

The max number of DESC would be DESC 50 for one of the BASENAME. STUFF not work in this scenario, as it is creating error as

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

Code i tried:

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 (partition by [Generic Name] 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);

is there any other way to achieve the desired output.

Pivot using dynamic SQL? Several examples on SO already for SQL server. Here's [one](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) or is it the stuff in the dynamic pivot that's not working? As you didn't post the example you're running into problems with I'm unsure how you're using stuff here. — xQbert, Nov 13 '17 at 14:09
i tried like: SET @Sel = STUFF((SELECT distinct ',' + QUOTENAME([Description]) + ' AS DESC'+CAST(row_number () over (order by ([Description])) as varchar(250)) FROM STAGING_TEMP WHERE [Generic Name]='Winter Traction Device – LT Truck Tire' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); — akhrot, Nov 13 '17 at 14:12
but this error: The number of elements in the select list exceeds the maximum allowed number of 4096 elements. — akhrot, Nov 13 '17 at 14:13
You said in the question desc wouldn't have more than 50 elements. Yet the error implies you have more than 4096. It seems like you're not partitioning by the basename correctly; and instead having all descriptions be listed in one row. Each row should have at most 51 columns. BaseName + max of 50 desc. Your logic isn't partitioning by the base name correctly. Perhaps have just add partitioni by basename to your logic `row_number() over (partition by basename order by description)` Or review the pivot in the 'One' example in 1st comment — xQbert, Nov 13 '17 at 14:15
@xQbert: i tried like this: = STUFF((SELECT distinct ',' + QUOTENAME([Description]) + ' AS DESC'+CAST(row_number () over (partition by [Generic Name] order by [description]) as varchar(250)) FROM STAGING_TEMP FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); , but the same error. — akhrot, Nov 13 '17 at 14:21
Pretty sure the column list in the select part and the PIVOT IN part should be the same. Have you tried Printing the generated SQL to figure out what exactly is wrong? — user6144226, Nov 13 '17 at 14:38
Hi Techie, may someone please suggest if any other way to do this. — akhrot, Nov 13 '17 at 17:25

score 0 · Answer 1 · answered Nov 13 '17 at 14:11

0

Sounds like you are hitting a limit of SQL Server SELECT / INSERT statements as defined in the documentation though you haven't provided your STUFF() code snippet.

Columns per SELECT statement        4,096   
Columns per INSERT statement        4,096

Also note, the number of columns per a nonwide table is 1,024.

answered Nov 13 '17 at 14:11

S3S

24,809
5
26
45

is there no way to do in sql. Please suggest – akhrot Nov 13 '17 at 14:22

Stuff Column Value handling select list exceeds the maximum allowed number of 4096 elements

1 Answers1