I have a stored procedure that uses a dynamic pivot table returns a dataset as follows.
I want to use ORM entity objects to get a data table from the returned value to populate MVC grid.
Is that possible?
Regards
ajay
Entity object (using wizard to import SP)
SPC:
-- =============================================
-- Author: <
Author
,,Name>
-- Create date: <
Create
Date,,>
-- Description: <
Description
,,>
-- =============================================
ALTER PROCEDURE [dbo].[procGetMonthlyConsumption]
(
@pPeriodStart nvarchar(4)='2009',
@pPeriodEnd nvarchar(4)='2014'
)
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET NOCOUNT ON;
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(convert(nvarchar(3),BillStartPeriod,107))
FROM dbo.table_energy_consumption
--where '],[' + ltrim(convert(nvarchar(4),BillStartPeriod,120))='],[' + @pBillPeriod
ORDER BY '],[' + ltrim(convert(nvarchar(3),BillStartPeriod,107))
FOR XML PATH('')
), 1, 2, '') + ']'
-- @pBillPeriod varchar(4)='2011'
SET @query =
'SELECT * FROM
(SELECT ConsumerId,LeaseID, ltrim(convert(nvarchar(3),BillStartPeriod,107)) ConsumptionMonth, BillPeriodConsumption,ltrim(convert(nvarchar(4),BillStartPeriod,120)) as Saal
FROM dbo.table_energy_consumption
where YEAR(BillStartPeriod) >= '+@pPeriodStart+ '
and YEAR(BillEndPeriod) <='+@pPeriodEnd+ '
) src
PIVOT (SUM(BillPeriodConsumption) FOR ConsumptionMonth
IN ('+@listCol+') ) AS pvt order by Saal asc,Consumerid asc'
EXECUTE (@query)
RETURN
END