Es para hacer consultas de referencia cruzadas cosa que Transform y pivot mysql no los tiene.
CREATE DEFINER=`root`@`%` PROCEDURE `Pivot`(
IN tbl_name VARCHAR(99), -- table name (or db.tbl)
IN base_cols VARCHAR(99), -- column(s) on the left, separated by commas
IN pivot_col VARCHAR(64), -- name of column to put across the top
IN tally_col VARCHAR(64), -- name of column to SUM up
IN where_clause VARCHAR(99), -- empty string or "WHERE ..."
IN order_by VARCHAR(99) -- empty string or "ORDER BY ..."; usually the base_cols
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
-- Find the distinct values
-- Build the SUM()s
SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
-- select @subq;
SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
SET @cc3 = REPLACE(@cc2, '&t', tally_col);
-- select @cc2, @cc3;
SET @qval = CONCAT("'\"', val, '\"'");
-- select @qval;
SET @cc4 = REPLACE(@cc3, '&v', @qval);
-- select @cc4;
SET SESSION group_concat_max_len = 10000; -- just in case
SET @stmt = CONCAT(
'SELECT GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n") INTO @sums',
' FROM ( ', @subq, ' ) AS top');
select @stmt;
PREPARE _sql FROM @stmt;
EXECUTE _sql; -- Intermediate step: build SQL for columns
DEALLOCATE PREPARE _sql;
-- Construct the query and perform it
SET @stmt2 = CONCAT(
'SELECT ',
base_cols, ',\n',
@sums,
'\n FROM ', tbl_name, ' ',
where_clause,
' GROUP BY ', base_cols,
'\n', order_by
);
select @stmt2; -- The statement that generates the result
PREPARE _sql FROM @stmt2;
EXECUTE _sql; -- The resulting pivot table ouput
DEALLOCATE PREPARE _sql;
-- For debugging / tweaking, SELECT the various @variables after CALLing.
END
El asunto que cuando quiero recuperar los datos desde vb.net no los trae o me trae el texto de la variable @stmt2.
Llamo a este SP desde vb.net asi.
Private Function cargoSet(ByVal tb1 As String, ByVal tb2 As String, ByVal tb3 As String, ByVal tb4 As String, ByVal tb5 As String, ByVal tb6 As String, ByVal pTabla As String, ByVal proce As String)
If cont.conM.State = ConnectionState.Open Then
cont.conM.Close()
End If
cont.conM.Open()
Dim comS As New MySqlDataAdapter
comS.SelectCommand = New MySqlCommand
comS.SelectCommand.Connection = cont.conM
comS.SelectCommand.CommandText = proce
comS.SelectCommand.CommandType = CommandType.StoredProcedure
comS.SelectCommand.Parameters.Add("@tbl_name", MySqlDbType.VarChar, 99)
comS.SelectCommand.Parameters("@tbl_name").Value = tb1
'tbl_name.Direction = ParameterDirection.Input
comS.SelectCommand.Parameters.Add("@base_cols", MySqlDbType.VarChar, 64)
comS.SelectCommand.Parameters("@base_cols").Value = tb2
'base_cols.Direction = ParameterDirection.Input
comS.SelectCommand.Parameters.Add("@pivot_col", MySqlDbType.VarChar, 64)
comS.SelectCommand.Parameters("@pivot_col").Value = tb3
'pivot_col.Direction = ParameterDirection.Input
comS.SelectCommand.Parameters.Add("@tally_col", MySqlDbType.VarChar, 64)
comS.SelectCommand.Parameters("@tally_col").Value = tb4
'tally_col.Direction = ParameterDirection.Input
comS.SelectCommand.Parameters.Add("@where_clause", MySqlDbType.VarChar, 99)
comS.SelectCommand.Parameters("@where_clause").Val ue = tb5
'where_clause.Direction = ParameterDirection.Input
comS.SelectCommand.Parameters.Add("@order_by", MySqlDbType.VarChar, 99)
comS.SelectCommand.Parameters("@order_by").Value = tb6
'order_by.Direction = ParameterDirection.Input
comS.Fill(ds, pTabla)
End Function
Que es lo que esta mal?
