asked
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
the code snippet doesn't match the table definition - can you fix
When I run this I don't get an error - are you saying it is still broke?
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
@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.
18 People are following this question.
