I'm not quite sure how to describe this....I have a table with Part Number, Attribute, Attribute value. The part number is repeated multiple times - one row for each attribute it has. Some parts have 10 attributes, some have over 100. I have millions of parts, so if I just do a pivot, while that works it has two issues.
Many of the columns will be blank because those attributes do not apply to that part number
The result is too 'wide' meaning it can't fit into excel for use by others.
The way I've seen this solved in other places is create a sheet with the columns of Part Number, Attribute Name1, Attribute Value 1, Attribute Name 2 Attribute Value 2 and so on. Where the attributes for a given item are populated from 1 to N where n is the last attribute it has....of course the spreadsheet might be 100 columns wide with many of the columns being blank where the item only has 20 attributes, but that's ok.
So does anyone (@dgudkov ??) know how I can go from:
It can be done by unpivoting, and then re-pivoting the table by different columns.
The trick is to calculate new column names. The "Pivot" action sorts columns from left to right in ascending order. Therefore, we need to have synthetic sortable column names for pivoting to ensure the correct order of columns. Then the synthetic column names are replaced with the desired column names.
PS.
This is not the 1st time I see this output format. There was another post on the forum asking for a similar output, but I couldn't find it (it had a similar solution).
First - thank you this is great. Yes I also thought this was out there somewhere but searching yielded no results.
It's for a product information management (PIM) system that stores all the attributes about a given item. Many times this is used to feed an eCommerce system but it also has its place in ERP and MRP. Typically the attributes are defined at a taxonomy level and then items are assigned to the category. The issue becomes if someone wants to export items from many different categories you run into issues with excel width limitations unless your using some sort of text/csv file - which in that case you need some sort of software to read it (not Excel)...and usually those softwares like Easymorph are not what your every day business user is going to have (or know how to use to get to analysis they need)...
Also some of these PIM systems allow for importing of data for many different items within different categories at the same time....and those templates are populated in some cases by hand so you need a way to handle this in an application that 99.99% of the user base knows how to use - Excel.
