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.
Thanks a lot
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:
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:
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.
answered Nov 13 at 08:08 PM