hi bosses i have used sql pivot to show employeewise product in grids. when i execute the query in management studio it is working fine but the problem is when i want to show data in jqgrid in with dynamic rows and columns it is not working. I am new in jqgrid.
here is my controller action method
public ActionResult FindOrderByEmployeeCall()
{
var query = (from fo in db1.FindOrderByEmp1()
select fo).ToList();
return Json(query, JsonRequestBehavior.AllowGet);
}
my ajax method for show data in jqgrid in view page is
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
type: "POST",
url: "/Invoice/FindOrderByEmployeeCall/",
dataType: "json",
success: function(result){
var colD = result.couponStripList,
colM = result.colModelList;
$("#list").jqGrid({
//datatype: 'local',
data: "",
gridview: true,
colModel :colM,
height: "auto",
loadComplete: function(data){
alert('loaded');
},
loadError: function(xhr,status,error){
alert('error');
}
});
},
error: function(x, e){
alert(x.readyState + " "+ x.status +" "+ e.msg);
}
});
});
</script>
and my sql pivot query is
ALTER proc [dbo].[FindOrderByEmp1]
as
BEGIN
SET FMTONLY OFF;
DECLARE @query nvarchar(max)
DECLARE @Product NVARCHAR(max),@Product1 NVARCHAR(max)
SELECT @Product = STUFF(( SELECT distinct'],['+ rtrim(ProdId) FROM OrderDetails ORDER BY '],['+ rtrim(ProdId) FOR XML PATH('')), 1, 2, '')+']'
set @Product1 =SUBSTRING(( select distinct ',IsNull(['+rtrim(ProdId)+'],0) as ['+rtrim(ProdId)+']' from OrderDetails for xml path('')),2,8000)
SET @query =
'SELECT EmpId,'+@Product1+' FROM
(
SELECT d.ProdId,m.EmpId,isnull(convert(int,d.Qty),0.0) as oqty
FROM OrderMaster m inner join OrderDetails d on m.OrdId=d.OrdId
)t
PIVOT (SUM(oqty)
FOR ProdId
IN ('+@Product+')) AS pvt'
EXECUTE (@query)
end
Please help me..
