quote:

---

Originally posted by cjcclee

visakh16,
Thanks! I have similar question. I have dynamic SQL from pivot table, I want to join the result with other query.

since the columns from dynamic pivot table is not fixed, I can not delare the temptable with column name, I tried your suggestion,
something like this

select * into #temptable execute (@pivottableSQL)
I got error: for select into statement, verify each column has a name.
What's correct syntax for this?

---

CREATE TABLE #temptable (col1 VARCHAR(32));
DECLARE @sql NVARCHAR(4000) = 'select 1 as col1;'
INSERT INTO #temptable EXEC (@sql);


DROP TABLE  #temptable;

quote:

---

Originally posted by James K

quote:

---

Originally posted by cjcclee

visakh16,
Thanks! I have similar question. I have dynamic SQL from pivot table, I want to join the result with other query.

since the columns from dynamic pivot table is not fixed, I can not delare the temptable with column name, I tried your suggestion,
something like this

select * into #temptable execute (@pivottableSQL)
I got error: for select into statement, verify each column has a name.
What's correct syntax for this?

---

SELECT INTO does not work in that context. Either you should create the table and then use the insert into ... exec syntax like in the example below, or you should modify your @pivottableSQL to insert the data into a global temp table or base table.

CREATE TABLE #temptable (col1 VARCHAR(32));
DECLARE @sql NVARCHAR(4000) = 'select 1 as col1;'
INSERT INTO #temptable EXEC (@sql);


DROP TABLE  #temptable;

---

quote:

---

Originally posted by maideen

Hi.
I need help. Below dynamic Pivot working fine in exec in mssql 2005. But I need the result insert data into dynamic table then Need to export into excel. Because, the column increase time to time based on data entry. Pls help me how to export directly or insert into dynamic table. Pls help me
Maideen
It is my store procedure

USE [collegeBac]
GO
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 #temp 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)
END

---

quote:

---

Originally posted by MuMu88

Try the modifications shown in red below:

quote:

---

Originally posted by maideen

Hi.
I need help. Below dynamic Pivot working fine in exec in mssql 2005. But I need the result insert data into dynamic table then Need to export into excel. Because, the column increase time to time based on data entry. Pls help me how to export directly or insert into dynamic table. Pls help me
Maideen
It is my store procedure

USE [collegeBac]
GO
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 #temp 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)
END

---

[/size=2]

---

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
@FDate AS DATETIME,
@TDate AS DATETIME
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 tbl_RCVDfor_Rajan 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
WHERE dbo.vwRCHeadDetails.RCDate >= ''' + @FDate + ''' and dbo.vwRCHeadDetails.RCDate <= ''' + @TDate
+ ''') 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 [Sheet1]') select * from tbl_RCVDfor_Rajan 
Drop Table tbl_RCVDfor_Rajan
Select 'Successfully Exported to Excel'
END

quote:

---

Originally posted by maideen

Mr.Visakh
Thank you.
It work fine. But If I define FDate and TDate as DateTime
The error is "Conversion failed when converting datetime from character string. "
But If I define FDate and TDate as nvarchar(20) It is work fine No error.
Is there any problem?
Maideen

---