May 29, 2015 at 9:57 am
Hi all,
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Please help. Here is a copy of the sctript below
-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
with External_Referrals
as (
Select
a.ClientID,
a.ReferralID,
a.ReferralSequenceID,
a.ReferralDateTime,
a.ReferralSourceDescription,
ROW_NUMBER() OVER(partition by ClientID ORDER BY a.ReferralDateTime asc) AS Row,
CAST(a.ClientID AS VARCHAR)+'_'+CAST(a.ReferralSequenceID AS VARCHAR) ExternalRef_SpellStartID
--into#External_Referrals
FROM[RiO_CECWMH].[dbo].[mh_MainReferralViewData]a
where
InternalReferralSourceFlag = 0
)
-- Construct the column list for the IN clause
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT [row] AS y FROM #External_Referrals) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT ClientID,
ReferralDateTime,
row
FROM#External_Referrals) AS D
PIVOT(max(ReferralDateTime) FOR [row] IN(' + @cols + N')) AS P order by 1 ;' ;
EXEC sp_executesql @sql;
GO
May 29, 2015 at 10:12 am
Hi again.
here is the original script before any adjustments.
-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
Select
a.ClientID,
a.ReferralID,
a.ReferralSequenceID,
a.ReferralDateTime,
a.ReferralSourceDescription,
ROW_NUMBER() OVER(partition by ClientID ORDER BY a.ReferralDateTime asc) AS Row,
CAST(a.ClientID AS VARCHAR)+'_'+CAST(a.ReferralSequenceID AS VARCHAR) ExternalRef_SpellStartID
into#External_Referrals
FROM[RiO_CECWMH].[dbo].[mh_MainReferralViewData]a
where
InternalReferralSourceFlag = 0
-- Construct the column list for the IN clause
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT [row] AS y FROM #External_Referrals) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT ClientID,
ReferralDateTime,
row
FROM#External_Referrals) AS D
PIVOT(max(ReferralDateTime) FOR [row] IN(' + @cols + N')) AS P order by 1 ;' ;
EXEC sp_executesql @sql;
GO
May 29, 2015 at 10:53 am
I can understand why you can't make this a view, but what error are you getting when trying to create a stored procedure, and please post the code you tried to create a stored procedure.
May 29, 2015 at 11:13 am
Hi Lynn,
Thanks for the response.
I didn't save the proc script. I just tried a few permutations but the error message was something like incorrect syntax near "set"
I will try to recreate the proc later and send an updated error message.
Regards
May 29, 2015 at 11:35 am
Hi again lynn,
the error message is
Msg 102, Level 15, State 1, Procedure ReferralsDynamicPivot, Line 53
Incorrect syntax near ';'.
......
Here is my attempt at convertring it to a proc
Thanks for your time.
Create proceduredbo.ReferralsDynamicPivot
as
begin
-- Dynamic PIVOT
--IF OBJECT_ID('#External_Referrals') IS NULL
--DROP TABLE #External_Referrals;
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
--with External_Referrals
--as(
Select
a.ClientID,
a.ReferralID,
a.ReferralSequenceID,
a.ReferralDateTime,
a.ReferralSourceDescription,
ROW_NUMBER() OVER(partition by ClientID ORDER BY a.ReferralDateTime asc) AS Row,
CAST(a.ClientID AS VARCHAR)+'_'+CAST(a.ReferralSequenceID AS VARCHAR) ExternalRef_SpellStartID
into#External_Referrals
FROM[RiO_CECWMH].[dbo].[mh_MainReferralViewData]a
where
InternalReferralSourceFlag = 0
--)
-- Construct the column list for the IN clause
begin
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT [row] AS y FROM #External_Referrals) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT ClientID,
ReferralDateTime,
row
FROM#External_Referrals) AS D
PIVOT(max(ReferralDateTime) FOR [row] IN(' + @cols + N')) AS P order by 1 ;' ;
EXEC sp_executesql @sql ;
GO
May 29, 2015 at 11:40 am
You have two unnecessary BEGIN statements and no END statements. You can remove the two BEGINs.
June 1, 2015 at 3:04 am
Thanks Lynn,
The procedure works now after taking your advice and ending the begins.
best wishes
A
June 3, 2015 at 1:59 am
Just to make your code a little cleaner,
DECLARE @T AS TABLE (y INT NOT NULL PRIMARY KEY);
DECLARE @cols AS NVARCHAR(MAX)
,@y AS INT /* not used */
,@sql AS NVARCHAR(MAX);
Note that the @y variable is not subsequently used in the procedure. Removing this of course eliminates the potential for confusion over it.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply