April 16, 2017 at 9:06 am
I have an application whereby I have data created in a series of views that has to be transformed into a Crosstab. However there may me additions to data that is represented as columns in the crosstab output. To that end I have sourced code that will create my crosstab in a dynamic fashion and hence will accommodate the increase in columns going forward. The code posted below has been tested and works fine and is designated as a Stored Procedure.
USE [Butchers]
GO
/****** Object: StoredProcedure [dbo].[WarehouseActivityCrosstab] Script Date: 16/04/2017 15:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WarehouseActivityCrosstab]
AS
DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
DECLARE @UniqueActivityNameToPivot NVARCHAR(MAX) = N'' --Variable to hold unique activity to be used in PIVOT clause
DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
--Extarct unique activity names with pivot formattings
SELECT @UniqueActivityNameToPivot = @UniqueActivityNameToPivot + ', [' + COALESCE(ActivityName, '') + ']' FROM (SELECT DISTINCT ActivityName FROM dbo.viewWarehouseTasksOutput)DT
SELECT @UniqueActivityNameToPivot = LTRIM(STUFF(@UniqueActivityNameToPivot, 1, 1, '')) --Remove first comma and space
--Generate column names to be put in SELECT list with NULL handling and aliases also
SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE(ActivityName, '') + '], 0) AS [' + ActivityName + ']'
FROM (SELECT DISTINCT ActivityName FROM dbo.viewWarehouseTasksOutput)DT
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT ActualDateStarted, WorkerID '
+ @PivotColumnsToSelect +
'
FROM dbo.viewWarehouseTasksOutput
PIVOT
(
SUM(ActualTimeNeeded) FOR
ActivityName IN
(' + @UniqueActivityNameToPivot + ')
) AS PVT
'
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)
Now I need to extract this data and put it into either a table or view which needs to be updated before it is displayed within my application.
I did a lot of research and found the next piece of code which purported to this and it was stated would deal with a "Dynamic" input.
SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=172-25-109-101\SQLEXPRESS;Trusted_Connection=yes;',
'EXEC Butchers.dbo.WarehouseActivityCrosstab');
-- Select Table
SELECT *
FROM #TestTableT;
However when I run this code I get the following error message.
Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because statement 'EXEC (@SQLStatement)' in procedure 'WarehouseActivityCrosstab' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
However using "WITH RESULTS SETS" implies that the output has to be defined in terms of columns etc.Given that this was supposed to accept a Dynamic input I ma at a loss!
Can anyone help me with this challenge.
April 17, 2017 at 6:43 am
carltedd 74728 - Sunday, April 16, 2017 9:06 AMI have an application whereby I have data created in a series of views that has to be transformed into a Crosstab. However there may me additions to data that is represented as columns in the crosstab output. To that end I have sourced code that will create my crosstab in a dynamic fashion and hence will accommodate the increase in columns going forward. The code posted below has been tested and works fine and is designated as a Stored Procedure.
USE [Butchers]
GO
/****** Object: StoredProcedure [dbo].[WarehouseActivityCrosstab] Script Date: 16/04/2017 15:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WarehouseActivityCrosstab]
ASDECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
DECLARE @UniqueActivityNameToPivot NVARCHAR(MAX) = N'' --Variable to hold unique activity to be used in PIVOT clause
DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
--Extarct unique activity names with pivot formattings
SELECT @UniqueActivityNameToPivot = @UniqueActivityNameToPivot + ', [' + COALESCE(ActivityName, '') + ']' FROM (SELECT DISTINCT ActivityName FROM dbo.viewWarehouseTasksOutput)DT
SELECT @UniqueActivityNameToPivot = LTRIM(STUFF(@UniqueActivityNameToPivot, 1, 1, '')) --Remove first comma and space
--Generate column names to be put in SELECT list with NULL handling and aliases also
SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE(ActivityName, '') + '], 0) AS [' + ActivityName + ']'
FROM (SELECT DISTINCT ActivityName FROM dbo.viewWarehouseTasksOutput)DT
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT ActualDateStarted, WorkerID '
+ @PivotColumnsToSelect +
'
FROM dbo.viewWarehouseTasksOutput
PIVOT
(
SUM(ActualTimeNeeded) FOR
ActivityName IN
(' + @UniqueActivityNameToPivot + ')
) AS PVT
'
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)Now I need to extract this data and put it into either a table or view which needs to be updated before it is displayed within my application.
I did a lot of research and found the next piece of code which purported to this and it was stated would deal with a "Dynamic" input.
SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=172-25-109-101\SQLEXPRESS;Trusted_Connection=yes;',
'EXEC Butchers.dbo.WarehouseActivityCrosstab');
-- Select Table
SELECT *
FROM #TestTableT;However when I run this code I get the following error message.
Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because statement 'EXEC (@SQLStatement)' in procedure 'WarehouseActivityCrosstab' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.However using "WITH RESULTS SETS" implies that the output has to be defined in terms of columns etc.Given that this was supposed to accept a Dynamic input I ma at a loss!
Can anyone help me with this challenge.
Fortunately or unfortunately, there's only one way to deal with taking data from a dynamic SQL statement into a table. You'll have to use dynamic SQL to drop and re-create the table for each and every execution, in a manner not terribly dissimilar to what you did for the PIVOT portion of your query. You obviously would NOT be able to have a table just sit there, and expect it to handle whatever columns you choose to send it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply