Announcement

Collapse
No announcement yet.

Pdf Sql Builder-Crosstab

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pdf Sql Builder-Crosstab

    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.

    Capture.jpg

    Anyone please help for coding in sql builder.

  • #2
    Which database? Since this is a bit more tricky...

    Comment


    • #3
      The database is Mysql. Please help

      Comment


      • #4
        http://dev.mysql.com/doc/refman/5.0/...5Fgroup-concat

        http://stackoverflow.com/questions/1...n-a-result-set

        http://greenash.net.au/thoughts/2012...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.

        Comment


        • #5
          I try the GROUP_CONCAT in table2 but it result to the following


          concat.jpg

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

          Need more help

          Comment

          Working...
          X