Tranpose Columns and Join

[mcha54]

I have some data in a crosstab format that I'd like to transpose. Example workflow attached. I'm having some trouble stitching it back together. Also, is there a better way to transpose to future-proof it if new columns were added?

Input:

Id

DATE

SKU

A-QTY

A-RECIPIENT

A-SHIPTO

A-COURIER

A-TRACKINGNO

B-QTY

C-QTY

BC-RECIPIENT

BC-SHIPTO

BC-COURIER

BC-TRACKINGNO

2,697,226,972

6/11/2020

ZZ5677Z010

1

JOHN DOE

2 Macquarie Street

UPS

12345

1

JANE DOE

221 B Baker St

DHL

54321

2,697,326,973

6/11/2020

ZZ5691ZPFU

1

JOHN DOE

2 Macquarie Street

UPS

12345

1

JANE DOE

221 B Baker St

DHL

54321

Desired Output:

Id	DATE	SKU	QTY	RECIPIENT	SHIPTO	COURIER	TRACKINGNO
2,697,226,972	6/11/2020	ZZ5677Z010	1	JOHN DOE	2 Macquarie Street	UPS	12345
2,697,226,972	6/11/2020	ZZ5677Z010	1	JANE DOE	221 B Baker St	DHL	54321
2,697,326,973	6/11/2020	ZZ5691ZPFU	1	JOHN DOE	2 Macquarie Street	UPS	12345
2,697,326,973	6/11/2020	ZZ5691ZPFU	1	JANE DOE	221 B Baker St	DHL	54321

[DavidP · Accepted solution]

Here's my attempt. The fact that B and C are lumped together for some fields made it a bit trickier.

Let me know if this works for you.

[smoskowitz]

@mcha54 

You might want to add a record ID before transposing. This will allow you to easily summarize it back to the original format -- if that's what you wish do. Let me know if you need more help.

Seth