Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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;
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;