Cross-tab or Transpose of blocks of columns (load data into excel)

[timewaste]

Hi Peers,

I am trying to transpose the data into 3 sets to write back in to excel. Please see below for my source data:

V	ED	D	F	SF	SPH	T	H
R	B	G	NC	Te	PH	M	14.4
R	B	G	NC	Te	PH	A	1.6
R	B	G	NC	Te	PH	V	0
R	B	A	NC	Bu	PH	M	16
R	B	A	NC	Bu	PH	A	0
R	B	A	NC	Bu	PH	V	0
R	B	A	NC	Zi	PH	M	0
R	B	A	NC	Zi	PH	A	0
R	B	A	NC	Zi	PH	V	0

and my output must look like:

	V	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B
	D	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	16	0	0	0	0	0

How can I achieve this? Please advise.

TIA!

[danilang]

Thanks for the update.  I'm looking at it in my spare time.  There's a key manipulation step that I'm missing, but i'll keep thinking about it.

Dan

[timewaste]

Sure @danilang looking forward to it!

[danilang · Accepted solution]

Here you go.

It took a while to get the data columns lined up with the header ones while at the same time keeping the data values in separate rows when required 

It was a tough one!  You should submit it as a weekly challenge!

Dan

[timewaste]

@danilang Thanks a ton! Sure, I must submit it as a weekly challenge.

But, there is one minor glitch here. You see row numbers 8 and 10 in the output. They have the same SPH value as PH. So, they must be combined into 1 single row. In the output, it must be like this in row number 8:

SPH   PH   14.4   1.6   0   2.5   6   0   0   0   0

and row number 10 must be avoided... similarly in all cases whenever SPH row value repeats. I think the change needs to happen in Building Group stage. Here the group number is calculated based on SF values of each row. But, if you could mention where exactly I need to change in order to accommodate that, it would be great!

if isnull([Row-1:GroupNumber]) then
1
elseif [Row-1:SF] != [SF] then
[Row-1:GroupNumber]+1
else
[Row-1:GroupNumber]
endif

But, nevertheless, you have been an immense help! Appreciate your support throughout.