x

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

Hello, DDL:

 CREATE  TABLE MYTABLE
     (
     BASENAME varchar(150),
     DESCRIPTION varchar(150)
     )
 
     insert MYTABLE
 
     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 for each BASENAME would be 50. The code below is working fine for small set of data when i filtered using where clause. But error occured when removed where clause, even after partition by BaseName.

Error: select list exceeds the maximum allowed number of 4096 elements, i aware this is the limit of sql server in select. is there any way to handle it, or any other way to achieve desired output.

Code:

     DECLARE @Cols NVARCHAR(MAX);
 DECLARE @SQL NVARCHAR(MAX);
 DECLARE @Sel NVARCHAR(MAX);
 
 
 
 SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME([Description]) 
             FROM MYTABLE 
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'');
 SET @Sel = STUFF((SELECT distinct ',' + QUOTENAME([Description]) + 
 ' AS DESC'+CAST(row_number () over (partition by BASENAME order by [description]) as varchar(250))
             FROM MYTABLE 
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,''); 
 
 
 
 
 SET @SQL = N'
 SELECT DISTINCT BASENAME, '+ @Sel+N'
 FROM MYTABLE
     pivot
     (
       MAX(Description) For Description IN ('+ @Cols + N')
 
     ) P';
 
 EXECUTE( @SQL);

Thanks a lot

more ▼

asked Nov 13 at 04:56 PM in Default

avatar image

AlexKlap
441 12 18

the code snippet doesn't match the table definition - can you fix

Nov 13 at 04:59 PM Kev Riley ♦♦

Fixed Kev. The worst part is even after applying partition by BASENAME, error occured, Thank you very Much for your suggestion.

Nov 13 at 05:05 PM AlexKlap

When I run this I don't get an error - are you saying it is still broke?

Nov 13 at 07:49 PM Kev Riley ♦♦

@Kev Riley ♦♦ I think OP means broke in the sense that it does not work as expected. I mentioned the source of the problem in my answer below (the list of columns for pivot was too generous).

Nov 13 at 08:11 PM Oleg

Ah! thanks for explaining @Oleg

Nov 13 at 08:59 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The script to figure out the column list should not be dynamic. Dynamic select of of all descriptions is not correct, as it returns the list of all descriptions while it just needs to return the list of column headers (in your case 50 items in the list at most). This will eliminate the problem with too many items in the select list. The actual PIVOT is dynamic, it needs to be in order to accommodate the list of the column headers which is data dependent.

Let's start from the sample table in question and add some rows into it. The script below is not a part of the solution, it is just a data mock up:

 -- Multiply the original descriptions by 5 and add back to the set
 insert into dbo.MyTable
 select BaseName, [Description] + '_0' + cast(t.n as varchar) 
     from dbo.MyTable cross join (values (1), (2), (3), (4), (5)) t(n);
 
 -- Multiply the base names by 100 and add back to the set. The final
 -- result is there are 200 base names, some of which have 30, and some
 -- 48 descriptions per base name for a total of 7,800 rows in the table.
 insert into dbo.MyTable
 select 
     mt.BaseName + '_' + replace(str(t.number, 2), ' ', '0'), [Description]
     from dbo.MyTable mt cross join master.dbo.spt_values t 
     where t.[type] = 'P' and t.number between 1 and 99;
 go

The script above inserted some rows so now there are 100 base names with 30 descriptions each and 100 base names with 48 descriptions each. Here is the solution:

 -- Need to figure out the maximim number of descriptions per base name.
 -- This number will determine the number of description columns in the output
 declare @cols varchar(500), @sql nvarchar(max);
 declare @descCount int = (
     select 
         top 1 count(1) ColumnCount 
         from dbo.MyTable 
         group by BaseName 
         order by ColumnCount desc
 );
 
 select
     @cols = stuff((
         select
             ', ' + quotename('Desc' + cast(number as varchar)) 
             
             from master.dbo.spt_values
             where [type] = 'P' and number between 1 and @descCount
             for xml path('')
 
     ), 1, 2, ''),
     @sql = '
         select
             *
             from (
                 select
                     BaseName, [Description], ''Desc'' + cast(
                         row_number() over (partition by BaseName order by [Description]) 
                         as varchar(10)
                     ) DescCol
                     from dbo.MyTable
             ) src
             pivot (max([Description]) for DescCol in (' + @cols + ')) pvt;
     ';
 
 exec sp_executesql @statement = @sql;
 go

The script in the solution will return 1 row per base name and as many columns as the most descriptions for any single base name. The list of column headers is dynamic even though it was populated via static select statement.

Hope this helps.

Oleg

more ▼

answered Nov 13 at 08:08 PM

avatar image

Oleg
18.9k 3 7 28

Thanks a ton Oleg.... awesome learning,

Nov 14 at 06:22 AM AlexKlap

@AlexKlap I received email message from the site reading that you posted a comment. The comment text mentioned something about the syntax error in the script. I am not sure what could be the cause of it, because I checked and executed my script before posting, to make sure it does exactly what it needs to do.

6 days ago Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x114

asked: Nov 13 at 04:56 PM

Seen: 25 times

Last Updated: 6 days ago

Copyright 2017 Redgate Software. Privacy Policy