Normalizing data with multiple fields

[xarnax]

Hello,

I need help with converting a large amount of data with multiple fields. Please see below sample of what I'm trying to achieve:

Before:

Project	Issue	Preparer	Cook	Examiner	Date
A	CD	John	John	Ben	1-Jan
B	EF	Ben	Tom	Kelly	4-Apr

After:

Role	Name	Project	Issue	Date
Preparer	John	A	CD	1-Jan
Cook	John	A	CD	1-Jan
Examiner	Ben	A	CD	1-Jan
Preparer	Ben	B	EF	4-Apr
Cook	Tom	B	EF	4-Apr
Examnier	Kelly	B	EF	4-Apr

I've searched around the community and played around with the transpose and cross tab functions but still couldn't get to the resulting format that I needed. Would anyone please kindly help out? Much appreciated!

Thank you,

Arna

[MarqueeCrew · Accepted solution]

@xarnax,

Here's your solution:

The configuration of the TRANSPOSE was a little tricky.  After that, I used a SELECT to rename and re-order the data.  I've attached two versions (v 11 and v 11.3) for you.

Cheers,

Mark

[xarnax]

Thank you so much! Appreciated it! This is exactly what I was looking for :-)

[MarqueeCrew]

Glad to help!

Cheers,

Mark