What you can do is.. you can create a table Where you want to store the data.... After Creating the Table....
Change your Procedure like this...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
Delete from CreatedTable
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
'[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails
ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
PIVOT ( SUM(Amount) FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
Insert into CreatedTable(Column1,Column1,Column1,..Columnn)
EXECUTE(@PivotTableSQL)
Select 'Succesfully Inserted into the Table"
END
U can Export to Excel Sheet from the CreatedTable....Now For Exporting to Excel Sheet u can use this...
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from CreatedTable
For More information...
[
Check this link]
Version 2:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
'[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT * into TableName FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails
ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
PIVOT ( SUM(Amount) FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
EXECUTE(@PivotTableSQL)
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from TableName
Drop Table TableName
Select 'Succesfully Exported to Excel'
END
Hope this Helps.....