how to transpose tables from row to column

[rupaliqlik]

Hi ,

Hi Team,

I want to convert above hierarchy in horizontal format

BMNAME	BMCODE	NSMNAME	NSMCODE	TMNAME	TMCODE	DMNAME	DMCODE	MDONAME	MDOCODE
Bhandurge Ganesh INPU	74	GAUTAM RAJESH KUMAR INPU	114	Manoj Tryambak Savakare INPU	361	Nichat Dinesh INPU	68	Pratik Ghuge	5960

@abhijitnalekar 

[abhijitnalekar]

Hi @rupaliqlik,

Please check below code.

TableD:
LOAD FullName,
RoleName,
RoleName&'Code' as RoleNameC,
RoleName&'Name' as RoleNameN,
User_T
FROM
[C:\Users\Rugved\Desktop\Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (TableD)
LOAD FullName,
RoleName,
RoleNameC,
RoleNameN,
Count(RoleName) as Count,
sum(User_T) as Value

Resident TableD
Group By RoleNameC,RoleNameN, FullName, RoleName;

FinalTable:
LOAD DISTINCT FullName
Resident TableD;

FOR i = 1 to FieldValueCount('RoleNameC')

LET vState = FieldValue('RoleNameC', $(i));

Left Join (FinalTable)
LOAD DISTINCT FullName,
Value as [$(vState)]
Resident TableD
Where RoleNameC = '$(vState)';
NEXT

FOR i = 1 to FieldValueCount('RoleNameN')

LET vState = FieldValue('RoleNameN', $(i));

Left Join (FinalTable)
LOAD DISTINCT FullName,
FullName as [$(vState)]
Resident TableD
Where RoleNameN = '$(vState)';

NEXT

DROP Table TableD;