SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 insert into table from Dynamic Pivot Store Procedu
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maideen
Starting Member

Malaysia
8 Posts

Posted - 07/16/2013 :  08:30:38  Show Profile  Reply with Quote
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 * 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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/16/2013 :  13:48:00  Show Profile  Reply with Quote
use SELECT INTO syntax to create table on the fly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

cjcclee
Starting Member

33 Posts

Posted - 07/16/2013 :  14:35:53  Show Profile  Send cjcclee an AOL message  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/16/2013 :  15:50:56  Show Profile  Reply with Quote
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;
Go to Top of Page

cjcclee
Starting Member

33 Posts

Posted - 07/16/2013 :  16:35:35  Show Profile  Send cjcclee an AOL message  Reply with Quote

Thank you! The columns returned from pivot table are not fixed.
It maybe have col1, col2, col3 or it maybe have col1,col2,col3,col4 or more. If create temp table, I do not know how many columns to be declared.

How can create temp table on the fly using results from execute dynamic SQL?




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;


Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/16/2013 :  21:16:41  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/17/2013 :  02:08:36  Show Profile  Reply with Quote
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]


This will make # tales out of scope once outside the dynamic sql. So I think you might need to use ## tables instead.
Also beware of issues due to concurrent execution etc.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maideen
Starting Member

Malaysia
8 Posts

Posted - 07/18/2013 :  00:47:59  Show Profile  Reply with Quote
Thanks Mr. visakh16.

Now SP works fine

Problem solves using "Select into tablename"

Regards
Maideen


Go to Top of Page

maideen
Starting Member

Malaysia
8 Posts

Posted - 07/18/2013 :  00:54:00  Show Profile  Reply with Quote
Hi All
There is one problem in SP when execute. I have added the date and export into Excel.
Must declare the scalar variable "@FDate".
Here is my SP. Pls advice where I did wrong...
Maideen



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

Edited by - maideen on 07/18/2013 00:54:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/18/2013 :  01:29:51  Show Profile  Reply with Quote
The variable @Fdate will be out of scope when used inside dynamic sql.
so it should be this


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maideen
Starting Member

Malaysia
8 Posts

Posted - 07/18/2013 :  20:16:32  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 07/19/2013 :  00:49:15  Show Profile  Reply with Quote
-- do the following modification
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
@FDate AS VARCHAR(50),
@TDate AS VARCHAR(50)
AS


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/19/2013 :  00:56:27  Show Profile  Reply with Quote
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


You should always try to use proper datatype for your fields/parameters. If they're accepting date values, the datatype has to be datetime or date
The error occurs when value you passed to variable cannot be correctly interpreted as a valid date value based on server locale settings. Safe bet is to use unambiguos iso format.

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000