Prepare stmt pivot SQL in grid's SQL section

Applications Grid / Reports
#1

Hello
I’m, trying to fill a grid with a pivot sql, using prepare stmt, but I can?t figure out how to put the sql statement in the sql section on the grid, can anyone shed some light?

SET @sql = (
    SELECT GROUP_CONCAT(DISTINCT 
        CONCAT(
            "sum(case when `idcatcxp`='", idcatcxp, "' then `monto` end) as `", `idcatcxp`, "`"
        )
    ) 
    FROM cxp
);

SET @sql = CONCAT("select idvehiculo, ", @sql, " from cxp group by `idvehiculo`");


PREPARE stmt FROM @sql;

EXECUTE stmt;

If I try to save this inside the SQL section of the grid SC throws an error on saving.

thnx in advance!

#2

I think it can’t be done since SC expects to create the fields from the SQL statement and this cannot be done because there’s no telling how many columns the SQL will return, any other suggestions?

#3

I created this code in a blank app

// Check for record
$check_sql = '
SET @sql = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT(
            "sum(case when `idcatcxp`=", idcatcxp, " then `monto` end) as `", `idcatcxp`, "`"
        )
    )
    FROM cxp
);

SET @sql = CONCAT("select idvehiculo, ", @sql, " from cxp group by `idvehiculo`");

PREPARE stmt FROM @sql;

EXECUTE stmt;

';

sc_lookup(rs, $check_sql,"conn_LoteAutomotriz");

   print_r($rs);
if (isset({rs[0][0]}))     // Row found
{
}
        else     // No row found
{
           echo 'nada';
}

If I run the sql in mysqlyog or mysql workbench, it returns data perfectly, but when excecuting the blank app it returns nothing.

Array ( ) nada

[ATTACH=JSON]{“alt”:“Click image for larger version Name: Captura.PNG Views: 0 Size: 76.5 KB ID: 89723”,“data-align”:“none”,“data-attachmentid”:“89723”,“data-size”:“full”,“title”:“Captura.PNG”}[/ATTACH]

Captura.PNG

#4

Try creating an sql procedure saving the data in a table, on SQL prepare grid