Cross Table

[Anonymous]

Hi All,

I have a table with following column

ID, Country, Score1, Score2, Score3, Comment1, Comment2, Comment3

101,INDIA, 4,5,4,qqqqqqqq,aaaaaaaaaaaa,zzzzzzzzzz

102,USA,3,2,5,wwww,sssss,ccccc

soon.

Now i want output to be as shown below

ID, Country,Score, Comments

101,INDIA,4,qqqqqqqq

101,INDIA,5,aaaaaaaaaaaa

101,INDIA,4,zzzzzzzzzz

102,USA,3,wwww

102,USA,2,sssss

102,USA,5,ccccc

i tried Cross table but no luck..

[sunny_talwar · Accepted solution]

Try this:

Table:

CrossTable(Field, Value, 2)

LOAD * Inline [

ID, Country, Score1, Score2, Score3, Comment1, Comment2, Comment3

101,INDIA, 4,5,4,qqqqqqqq,aaaaaaaaaaaa,zzzzzzzzzz

102,USA,3,2,5,wwww,sssss,ccccc

];

FinalTable:

NoConcatenate

LOAD RowNo() as Key,

  ID,

  Country,

  Value as Score

Resident Table

Where WildMatch(Field, 'Score*');

Join (FinalTable)

LOAD RowNo() as Key,

  ID,

  Country,

  Value as Comment

Resident Table

Where WildMatch(Field, 'Comment*');

DROP Table Table;