Pdf Sql Builder-Crosstab

Applications Report PDF
#1

I have 2 tables as per below

Table 1
NAME STATUS
John Doe Single

Table 2
NAME ALLOWANCE AMOUNT
John Doe Transportation 100
John Doe Meal 200
John Doe Laundry 300

I need the following result after running the sql builder in the scriptcase pdf report

NAME STATUS TRANSPORTATION MEAL LAUNDRY
John Doe Single 100 200 300

Take note that the ALLOWANCE and AMOUNT data in table 2 need to pivot.

Attached is the file containing the initial sql query i made and the result than i need.

Anyone please help for coding in sql builder.

Capture.jpg

#2

Which database? Since this is a bit more tricky…

#3

The database is Mysql. Please help

#4

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

http://stackoverflow.com/questions/1759580/mysql-how-to-flatten-a-result-set

http://greenash.net.au/thoughts/2012/05/flattening-many-to-many-fields-for-mysql-to-csv-export/

etc… just search for mysql flatten result and you’ll find enough hints. Then try it out using a normal mysql workbench or otherquery tool and then try it in scriptcase.

#5

I try the GROUP_CONCAT in table2 but it result to the following

name allowance amount GROUP_CONCAT(allowance) GROUP_CONCAT(amount)
John Doe Transportation 100 Transportation,Meal,Laundry 100,200,300

Need more help

concat.jpg