Convert single column rows into multiple columns

[BI_Rockstar_265]

Can we get output table like this from input table.

[mfchmielowski]

Hi.

You can use subfield function. Something like this:

Result:

Load *, 

Subfield(Tmp, '#', 2) as value;

Load

Subfield(Column, ',', 1) as abcd-xyz,

Subfield(Column, ',', 2) as Tmp,

Subfield(Column, ',', 3) as status

Resident sourceTable;

[BI_Rockstar_265]

Hi,

As per the above suggestion,its giving only the column information but  i need the output table format.

Can you please help me on this.

[mfchmielowski]

TmpTable:
load
	*
	, subfield(headers,',',1) as headerOne
	, subfield(headers,',',2) as headerTwo
	, subfield(values, ',', 1) as valueOne
	, subfield(values, ',', 2) as valueTwo
;
load
	Column
	, subfield(Column,'#',1) as headers
	, subfield(Column,'#',2) as values
;
load * inline [
	Column
	abcd-xyz,STATUS#67,Delivered
	abcd-xyz,STATUS#42,OtherStatus
](delimiter is ';');

let headerOne = peek('headerOne',0,'TmpTable');
let headerTwo = peek('headerTwo',0,'TmpTable');

Result:
load
	valueOne as [$(headerOne)]
	, valueTwo as [$(headerTwo)]
Resident
	TmpTable
;

Drop table TmpTable;

You can check something like this one. But, you've mentioned only one row as example so i cannot determine that the column name will be correct for other rows. 

[BI_Rockstar_265]

Hi Thanks for the explanation.
but 
You have given static values and dynamically i need 

other records can contain many columns and values 

and finally as per the output table  multiple header with corresponding values

[mfchmielowski]

Hi.

Ok, this is better example. 

TmpTable:
load
	*
;
load
	Column
	, subfield(Column,'#',1) as headers
	, subfield(Column,'#',2) as values
;
load * inline [
	Column
	abcd-xyz,STATUS#67,Delivered
	sdsd-sds,wew-rere,status#55,null,cancelled
](delimiter is ';');

let rows = NoOfRows('TmpTable')-1;

Res:
load * inline [colToDrop];

for i =0 to $(rows)
	
	let headers = lower(peek('headers', $(i), 'TmpTable'));
	let values = lower(peek('values', $(i), 'TmpTable'));

	Concatenate(Res)
	load * inline [
		$(headers)
		$(values)
	] (delimiter is ',');
next i;

drop field colToDrop;
drop table TmpTable;