One group of rows showing too many elements in the enumeration error

[jblackcitrin]

I did a left outer join on two queries based on a number. The expanded table has a column with four repeating values. I pivoted that column without aggregating.

The pivot works fine except for one group of entries. I get the error "Expression.Error: There were too many elements in the enumeration to complete the operation."

I am able to run the query but the rows for that group show blanks on the pivoted columns.

I reviewed other people's posts with this issue but none cover when this error is selective. I don't have any duplicate column names in my data sources.

Any idea what could be causing this?

[ronrsnfld]

This, by Cam Wallace, seems to address your problem, in a more efficient way than other solutions.

[HotChilli]

Suggest you post a small sample of data in text form showing what the data looks like before the pivot and then what you want it to look like after.

A pivot tries to create rows based on the data in the table.  When 'Don't aggregate' is chosen and there are duplicate values that it can't separate with other data (e.g. with an index) it throws this error.