Transpose with grouping

Hi, I can’t figure out how to perform the following conversion. (Tried Pivot, Unpivot, Group, but nothing seems to work…)

Source data:

I need to group by seller and buyer and set columns as rows, as follows:

If this example is too complex, simplified version would just not have 2nd item. So, if I can solve this for one item, I think I can do the rest with derived tables and then union them back.

Simplified example:
Source:


Result:

Examples excel attached. All pointers and suggestions are appreciated. Book1.xlsx (11.3 KB)

Hi Ruslan,

one approach to transposing with grouping is concatenating group cells into one, then transposing, then splitting them back.

See the example below.

group-pivot.morph (6.0 KB)
Book1.xlsx (11.3 KB)

image

On a side note, I’m curious how do you deal with identical repeating column names (Item/Amount)? It’s a very rare case, because almost all systems/applications require column names to be different. How do the end users understand what Item/Amount is what?

Thanks for providing a solution.

The reason why we have identical column names is because we are dealing with pricing breaks.

Let’s say you want to order:

<100 items, then price per item is 5.5,
100-500 items, price per item 5,
500-999 items, price per item 4, etc.

Our internal systems know how to parse this, so that’s why we need it in a specific format. I will try to run your example to see if it works shortly.