Sort of transpose / Merge to the right

1

Hi

I have multiple records, where I want to keep the ID, but then transform / merge columns to the right.
Not sure how to do it.

Flat file:

Example
No Amt Qty
884 213,037 59
884 216,885 153
884 173,508 153
100 84,000 84
100 112,000 85
591 158,194 59
596 161,200 4
592 184,229 302
592 250,000 302
592 254,000 302
592 222,000 302
592 140,000 302

Transform to
No Amt Qty Amt2 Qty2 Amt3 Qty3 Amt4 Qty4 Amt5 Qty5
884 213,037 59 216,885 153 173,508 153
100 84,000 84 112,000 85
591 158,194 59
596 161,200 4
592 184,229 302 250,000 302 254,000 302 222,000 302 140,000 302

Thanks

2

How about that (below)?

2column-pivot.morph (5.1 KB)
Sandbox 1.dset (309 Bytes)

image
image.png1389×581 26.5 KB

The limitation of this method is that you have to know in advance the max number of value pairs for each [No].

3

Updated with reordering logic that can work with arbitrary number of value pairs per group (column [No]).

2column-pivot.morph (7.7 KB)

image
image.png1419×631 27.7 KB

4

Thank you. This is brilliant.
R

5

Sorry, missed a sorting action. Here you go.

2column-pivot.morph (7.9 KB)

image
image.png1051×449 11.1 KB