how to convert to cross table ?

[gauravgg]

Hi

I have the following table

i want to convert the "Original" table to "Output" table in the script .

Original Table

ID	Category	SubCategory	type
1	A	a1,a2,a3	Z
2	B	b1,b2,b3	N
3	C	c1,c2	Q
4	A	a1,a2	X

Output Table

ID	A	B	C	type
1	a1,a2,a3			Z
2		b1,b2,b3		N
3			c1,c2	Q
4	a1,a2			X

can anyone help me

Thanks in Advance

[Gysbert_Wassenaar]

Sounds like a bad idea to me, but if you insist you can try The Generic Load

[sunny_talwar]

I agree with gwassenaar‌, but here is another solution

Table:

LOAD * INLINE [

    ID, Category, SubCategory, type

    1, A, "a1,a2,a3", Z

    2, B, "b1,b2,b3", N

    3, C, "c1,c2", Q

    4, A, "a1,a2", X

];

FinalTable:

LOAD ID,

  type

Resident Table;

FOR i = 1 to FieldValueCount('Category')

  LET vField = FieldValue('Category', $(i));

  Left Join (FinalTable)

  LOAD ID,

  type,

  SubCategory as [$(vField)]

  Resident Table

  Where Category = '$(vField)';

NEXT

DROP Table Table;