December 18, 2015 at 4:06 pm
Hello,
I am trying to use a SELECT statement as my IN condition in a PIVOT and I am not having any luck:
SELECT DISTINCT * FROM [DWH].[ufnGetPerYear] (@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam)) as s
PIVOT(SUM(YearShare) FOR [Year] IN (SELECT [RegYear] from [SAPBW_DataWarehouse_R3].[DWH].[CUMappingColumnLkp] WHERE [RegReportPeriod] = ISNULL(@RegReportPeriod, 'RIIO-ED1'))
I feel like I am missing something very simple.
Any help would be much appreciated.
Thank you so very much,
Matt
December 21, 2015 at 5:30 am
I'm guessing you want to pivot on values that you do not know until the proc executes.
This might work, may be a syntax error but this is the general idea behind dynamic pivot column names.
DECLARE @sql AS NVARCHAR(4000);
DECLARE @col AS NVARCHAR(500);
DECLARE @RegReportPeriod as MydataType,@VolumeParam as MydataType,@RegStartQtrParam as MydataType,
@RegStartYearParam as MydataType,@RegEndQtrParam as MydataType,@RegEndYearParam as MydataType
Declare @params nvarchar(2000)='@col AS NVARCHAR(500),@RegReportPeriod as MydataType,@VolumeParam as MydataType,@RegStartQtrParam as MydataType,
@RegStartYearParam as MydataType,@RegEndQtrParam as MydataType,@RegEndYearParam as MydataType'
SELECT @col = ISNULL(@col + ', ', '')+QUOTENAME([RegYear])
FROM (SELECT distinct[RegYear] from [SAPBW_DataWarehouse_R3].[DWH].[CUMappingColumnLkp]
WHERE [RegReportPeriod] = ISNULL(@RegReportPeriod, 'RIIO-ED1')) AS window;
SET @sql =
N'SELECT DISTINCT S.* , @col
FROM [DWH].[ufnGetPerYear] (@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam)) as s
PIVOT(SUM(YearShare) FOR [Year] IN ( @col)) AS MyPivot';
EXEC sp_executesql @sql,@params,@col,@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam
December 21, 2015 at 6:49 am
Or you could use cross tabs instead of pivot.
DECLARE @sql nvarchar(max),
@params nvarchar(4000);
SET @params = N'@VolumeParam int,
@RegStartQtrParam int,
@RegStartYearParam int,
@RegEndQtrParam int,
@RegEndYearParam int'; -- Change datatypes accordingly
SET @sql = N'SELECT Somecolumns' --This are the columns that define the groups.
+ ( --This will concatenate the pivoted columns.
SELECT CHAR(10) + CHAR(9) + ',SUM(CASE WHEN [YEAR] = ' + QUOTENAME( [RegYear], '''') + ' THEN YearShare ELSE 0 END) AS ' QUOTENAME(RegYear)
from [SAPBW_DataWarehouse_R3].[DWH].[CUMappingColumnLkp]
WHERE [RegReportPeriod] = ISNULL(@RegReportPeriod, 'RIIO-ED1')
FOR XML PATH( ''), TYPE).value('.','nvarchar(max)') + CHAR(10)
+ 'FROM [DWH].[ufnGetPerYear] (@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam)) as s' + CHAR(10)
+ 'GROUP BY Somecolumns;';
--This is for debugging purposes
PRINT @sql;
--This is the actual execution of the query
EXECUTE sp_executesql @sql, @params, @VolumeParam, @RegStartQtrParam, @RegStartYearParam, @RegEndQtrParam, @RegEndYearParam;
Further reading:
Cross tabs and Pivots Part 1[/url]
December 21, 2015 at 9:59 am
Hello Mad Admin,
Let me try this suggestion.
Thank you so very much for your time and assistance.
Most sincerely,
Matt
December 21, 2015 at 9:59 am
Hello Luis,
I am most appreciative of your time and attention.
I am going to try out your suggestion. I will post back.
Thank you so much!
Matt
December 21, 2015 at 3:08 pm
Hello Mad Admin,
I think I am getting closer with your help. Here is what I have:
DECLARE @RigsVolTableParam varchar (10)
DECLARE @RigsVolRowParam float
DECLARE @RigsInputActionParam varchar (20)
DECLARE @VolumeParam numeric (17,2)
DECLARE @RegStartQtrParam varchar (2)
DECLARE @RegStartYearParam varchar (4)
DECLARE @RegEndQtrParam varchar (2)
DECLARE @RegEndYearParam varchar (4)
DECLARE @RigsDNOTXTSHParam varchar(10)
DECLARE @sql AS NVARCHAR(4000);
DECLARE @col AS NVARCHAR(500);
Declare @params nvarchar(2000)='@col AS NVARCHAR(500),@VolumeParam as numeric(17,2),@RegStartQtrParam as varchar(2),
@RegStartYearParam as varchar(4),@RegEndQtrParam as varchar(2),@RegEndYearParam as varchar(4)'
SELECT @col = ISNULL(@col + ', ', '')+QUOTENAME([RegYear])
FROM (SELECT distinct[RegYear] from [SAPBW_DataWarehouse_R3].[DWH].[CUMappingColumnLkp]
WHERE [RegReportPeriod] = ISNULL(@RegReportPeriod, 'RIIO-ED1')) AS window;
SET @sql =
N'SELECT * FROM (SELECT DISTINCT * FROM [DWH].[ufnGetPerYear] (' + CAST(@VolumeParam AS VARCHAR(20)) + ',' + CAST(@RegStartQtrParam AS VARCHAR(20)) + ','
+ CAST(@RegStartYearParam AS varchar(20)) + ',' + CAST(@RegEndQtrParam AS VARCHAR(20)) + ',' + CAST(@RegEndYearParam AS varchar(20)) +') as s
PIVOT(SUM(YearShare) FOR [Year] IN ( ' + CAST(@col AS VARCHAR(500)) + ')) AS MyPivot) AS A';
PRINT @sql
If Object_Id('Tempdb..#TableFinal') is not null drop table #TableFinal
--Submitting Volume values from Intermediate table table above to calculation function and returning the final table with the values to update
Create Table #TableFinal
(
--RigsVolTable varchar(10),
--RigsVolRow varchar(20),
--RigsInputAction varchar(20),
--RigsDNOTXTSH varchar(10),
PlnVol1 numeric (17,2) null,
PlnVol2 numeric (17,2) null,
PlnVol3 numeric (17,2) null,
PlnVol4 numeric (17,2) null,
PlnVol5 numeric (17,2) null,
PlnVol6 numeric (17,2) null,
PlnVol7 numeric (17,2) null,
PlnVol8 numeric (17,2) null
)
DECLARE @i int
SET @i = 1
While @i < (SELECT COUNT (*) FROM #TableOfRigTableRigRowRigInputActionValues) + 1
BEGIN
SELECT @RigsVolTableParam = RigsVolTable
, @RigsVolRowParam = RigsVolRow
, @RigsInputActionParam = RigsInputAction
, @VolumeParam = Volume
, @RegStartQtrParam = RegStartQtr
, @RegStartYearParam = RegStartYear
, @RegEndQtrParam = RegEndQtr
, @RegEndYearParam = RegEndYear
,@RigsDNOTXTSHParam = RigsDNOTXTSH
FROM #TableOfRigTableRigRowRigInputActionValues
WHERE ID = @i
INSERT INTO #TableFinal
EXEC sp_executesql @sql,@params,@col,@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam
SET @i = @i + 1
END
-------------------
SELECT * FROM #TableFinal
-------------------
Only trouble with this, is that there are no values in the INSERT statement. It's as if the scope to variables is lost maybe? Only reason I say that, is if I actually hard code the values:
DECLARE @RigsVolTableParam varchar (10)
DECLARE @RigsVolRowParam float
DECLARE @RigsInputActionParam varchar (20)
DECLARE @VolumeParam numeric (17,2)
DECLARE @RegStartQtrParam varchar (2)
DECLARE @RegStartYearParam varchar (4)
DECLARE @RegEndQtrParam varchar (2)
DECLARE @RegEndYearParam varchar (4)
DECLARE @RigsDNOTXTSHParam varchar(10)
DECLARE @sql AS NVARCHAR(4000);
DECLARE @col AS NVARCHAR(500);
Declare @params nvarchar(2000)='@col AS NVARCHAR(500),@VolumeParam as numeric(17,2),@RegStartQtrParam as varchar(2),
@RegStartYearParam as varchar(4),@RegEndQtrParam as varchar(2),@RegEndYearParam as varchar(4)'
SELECT @col = ISNULL(@col + ', ', '')+QUOTENAME([RegYear])
FROM (SELECT distinct[RegYear] from [SAPBW_DataWarehouse_R3].[DWH].[CUMappingColumnLkp]
WHERE [RegReportPeriod] = ISNULL(@RegReportPeriod, 'RIIO-ED1')) AS window;
SET @sql =
N'SELECT * FROM (SELECT DISTINCT * FROM [DWH].[ufnGetPerYear] (' + CAST(120 AS VARCHAR(20)) + ',' + CAST(2 AS VARCHAR(20)) + ','
+ CAST(2021 AS varchar(20)) + ',' + CAST(3 AS VARCHAR(20)) + ',' + CAST(2023 AS varchar(20)) +') as s
PIVOT(SUM(YearShare) FOR [Year] IN ( ' + CAST(@col AS VARCHAR(500)) + ')) AS MyPivot) AS A';
PRINT @sql
If Object_Id('Tempdb..#TableFinal') is not null drop table #TableFinal
--Submitting Volume values from Intermediate table table above to calculation function and returning the final table with the values to update
Create Table #TableFinal
(
--RigsVolTable varchar(10),
--RigsVolRow varchar(20),
--RigsInputAction varchar(20),
--RigsDNOTXTSH varchar(10),
PlnVol1 numeric (17,2) null,
PlnVol2 numeric (17,2) null,
PlnVol3 numeric (17,2) null,
PlnVol4 numeric (17,2) null,
PlnVol5 numeric (17,2) null,
PlnVol6 numeric (17,2) null,
PlnVol7 numeric (17,2) null,
PlnVol8 numeric (17,2) null
)
DECLARE @i int
SET @i = 1
While @i < (SELECT COUNT (*) FROM #TableOfRigTableRigRowRigInputActionValues) + 1
BEGIN
SELECT @RigsVolTableParam = RigsVolTable
, @RigsVolRowParam = RigsVolRow
, @RigsInputActionParam = RigsInputAction
, @VolumeParam = Volume
, @RegStartQtrParam = RegStartQtr
, @RegStartYearParam = RegStartYear
, @RegEndQtrParam = RegEndQtr
, @RegEndYearParam = RegEndYear
,@RigsDNOTXTSHParam = RigsDNOTXTSH
FROM #TableOfRigTableRigRowRigInputActionValues
WHERE ID = @i
INSERT INTO #TableFinal
EXEC sp_executesql @sql,@params,@col,@VolumeParam,@RegStartQtrParam,@RegStartYearParam,@RegEndQtrParam,@RegEndYearParam
SET @i = @i + 1
END
-------------------
SELECT * FROM #TableFinal
-------------------
How do I maintain the variable values?
Thank you so much for your help!!!
Sincerely,
Matt
December 21, 2015 at 3:09 pm
Hello Luis,
I can't thank you enough for all your help.
I ran into some errors with the code you helped me with, but having seen your advice, it makes sense.
I am currently having an issue trying to use dynamic sql but ALSO maintain a temp variable's value when calling the dynamic sql.
Any ideas?
Thank you so much, again!
Most sincerely,
Matt
December 21, 2015 at 5:48 pm
matt.warren 33350 (12/21/2015)
Hello Luis,I can't thank you enough for all your help.
I ran into some errors with the code you helped me with, but having seen your advice, it makes sense.
I am currently having an issue trying to use dynamic sql but ALSO maintain a temp variable's value when calling the dynamic sql.
Any ideas?
Thank you so much, again!
Most sincerely,
Matt
Can you post what you have tried? Could you share ddl and sample data so we can test any code before posting? What do you mean with temp variable? A temp table? A table variable? Something else?
December 21, 2015 at 7:47 pm
matt.warren 33350 (12/21/2015)
Hello Luis,I can't thank you enough for all your help.
I ran into some errors with the code you helped me with, but having seen your advice, it makes sense.
I am currently having an issue trying to use dynamic sql but ALSO maintain a temp variable's value when calling the dynamic sql.
Any ideas?
Thank you so much, again!
Most sincerely,
Matt
Actually, yes. Help us help you. Please see the first link under "Helpful Links" in my signature lines below.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2015 at 9:04 am
Hello Luis,
I will do this.
Thank you so very much.
Sincerely,
Matt
December 22, 2015 at 9:09 am
Hello Jeff,
Thank you so much for your guidance.
I will post the CREATE script for the sp and function and any data.
Thank you again!
Matt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply