SQL Stored Procedure with Pivot tables

[JK_Bulk]

Hi Everybody!

Im trying to make the code below into a stored procedure. I would like to avoid defining it as one long string... This has developed into a problem: Sometimes the inputs in my procedure are defined as strings, and other times they are not (at least i think that is the problem)

[select pvt.Dates
     , pvt.1stProduct
     , pvt.2ndproduct
     , JR.discountd_prices
from 
	(
	select Name, Dates, Prices
	from dbo.tbSourceData
	) tb
pivot ( max(Prices) for Name in (1stProduct, 2ndProduct)
) pvt
left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = '1stProduct'
where pvt.Dates > getdate()-100]

I have rewritten this into the stored procedure:

alter proc dbo.spTest1
(
	@ProductID1 Varchar(15) = '1stProduct',
	@ProductID2 varchar(15) = '2ndProduct', 
	@DaysBack int = 100
)
as

select pvt.Dates
     , pvt.@ProductID1
     , pvt.@ProductID2
     , JR.discountd_prices
from 
	(
	select Name, Dates, Prices
	from dbo.tbSourceData
	) tb
pivot ( max(Prices) for Name in (@ProductID1, @ProductID2)
) pvt
left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = @ProductID1
where pvt.Dates > getdate()-@DaysBack

As an example in the line which begins with "pivot" i would like to use two product inputs, but as you see above, when defining column names for a pivot-table, they should not be written as 'Column1' and 'Column2' but as Column1 and Column2, preferably [Column1],[Column2].

Can anybody help me fix this, without turning the code into one long string?

• If i have forgotten anything else above, or i could do somethings in a smarter way, please let me know im new in all this

[gbritton]

You'll have to use Dynamic SQL to make this approach work.

something like:

declare @sql varchar(max) =
'
select pvt.Dates
     , pvt.@ProductID1
     , pvt.@ProductID2
     , JR.discountd_prices
from 
	(
	select Name, Dates, Prices
	from dbo.tbSourceData
	) tb
pivot ( max(Prices) for Name in (@ProductID1, @ProductID2)
) pvt
left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = @ProductID1
where pvt.Dates > getdate()-@DaysBack
'

set @sql = replace(@sql, '@ProductID1', @ProductID1)
set @sql = replace(@sql, '@ProductID2', @ProductID2)

exec sp_executesql @sql

[JK_Bulk]

Okay... But it would have been sweet to solve the problem without dynamic SQL You wrote 'You'll have to use Dynamic SQL to make this approach work.'

Is there another approach i could take, so that i wouldnt have to use dynamic SQL?

[gbritton]

@JK_Bulk

Is there another approach i could take, so that i wouldnt have to use dynamic SQL?

If there was, I would have showed you how.

[yosiasz]

can you explain the parameters @ProductID1, ProductID2 and what it is you are attempting to do with this stored procedure? what is your main aim you are trying to accomplish?

[JK_Bulk]

@ProductID1 and @ProductID2 could be 'icecream' and 'milk'. I want to show the prices for both products (and it should be possible to enter new products into the stored procedure, eg. 'Gasoline'). Furthermore i also want it to show the discounted product for a given set of dates the 'Dates' column

[yosiasz]

what @gbritton said is the way to go. there are other ways but very oogly. Why not pass your parameters as xml

DECLARE @xmlProducts xml; SET @xmlProducts = ' chicken nuggets cheese sticks '

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(value)
FROM dbo.XMLTable(@xmlProducts) where value is not null --XMLTable being a table function
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

create table #tbSourceData(Name varchar(50), Dates date, Prices money, discountd_prices money)

insert into #tbSourceData
select 'milk', '2016-01-01', 15.00, 14.50 union
select 'milk', '2016-02-01', 16.00, 14.50 union
select 'milk', '2016-03-01', 17.00, 14.50 union
select 'milk', '2016-04-01', 11.0, 14.500 union
select 'milk', '2016-05-01', 9.00, 14.50 union
select 'milk', '2016-06-01', 11.00, 14.50 union

select 'icecream', '2016-01-01', 15.00, 14.50 union
select 'icecream', '2016-02-01', 16.00, 14.50 union
select 'icecream', '2016-03-01', 17.00, 14.50 union
select 'icecream', '2016-04-01', 11.0, 14.500 union
select 'icecream', '2016-05-01', 9.00, 14.50 union
select 'icecream', '2016-06-01', 11.00, 14.50 union

select 'chicken nuggets', '2016-01-01', 15.00, 14.50 union
select 'chicken nuggets', '2016-02-01', 16.00, 14.50 union
select 'chicken nuggets', '2016-03-01', 17.00, 14.50 union
select 'chicken nuggets', '2016-04-01', 11.0, 14.500 union
select 'chicken nuggets', '2016-05-01', 9.00, 14.50 union
select 'chicken nuggets', '2016-06-01', 11.00, 14.50 union

select 'cheese sticks', '2016-01-01', 15.00, 14.50 union
select 'cheese sticks', '2016-02-01', 16.00, 14.50 union
select 'cheese sticks', '2016-03-01', 17.00, 14.50 union
select 'cheese sticks', '2016-04-01', 11.00, 14.50 union
select 'cheese sticks', '2016-05-01', 9.00, 14.50

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(value)
FROM dbo.XMLTable(@xmlProducts) where value is not null
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT pvt.Dates, ' + @cols + ' from
(
select Name, Dates, Prices
from #tbSourceData
) x
pivot
(
max(Prices) for Name in (' + @cols + ')
) pvt '

execute(@query)

[JK_Bulk]

Thanks yosiasz!