question

MarcoDellOca-1606 avatar image
0 Votes"
MarcoDellOca-1606 asked ErlandSommarskog commented

How to pass the "in" parameter of the pivot function as a variable

How to pass the "in" parameter of the pivot function as a variable in a table-valued function.
This is my function:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo]. [Ft_Pivot01AnalisiGlobale01]
(
@alladata as smalldatetime,
@ListToPivot as NVARCHAR (MAX)
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM (

select * from dbo.AnalisiGlobale01
where dataimport <= convert (nvarchar (10), @ alladata, 103)

  ) Analysis Results

  PIVOT (
    SUM ([SommaDiSellOut])
    FOR dataimport
    IN ([06/21/2021], [06/22/2021]) ---- insert @ListToPivot
  ) AS PivotTable

)
GO

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

The answer is that you can't. A query in SQL returns a table. A table represents an entity the columns are distinct attributes of that entity. That is, a query is wholly static. The number of columns are always the same, and their names always the same.

You can build a query dynamically to produce what you need in the spur of the moment, but then you cannot place this in a function. And you should ask yourself what you really want to do. A dynamic pivot is a non-relational operation, but is often done for presentational reasons. From that perspective, it is often better to better to return a relational data set and perform the pivoting in the presentation layer.

If you still want to do this in SQL, I have a primer here: https://www.sommarskog.se/dynamic_sql.html#pivot. But beware that dynamic SQL is nothing for SQL newbies, but it is an advanced operation.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarcoDellOca-1606 avatar image
0 Votes"
MarcoDellOca-1606 answered ErlandSommarskog commented

Thanks for the quick reply.

Is there a chance to create the Pivot Table-valued function with a stored procedure that passes "IN" parameters and then run it later or with the same stored procedure?

Basically I run the stored procedure that creates the Table-valued function and then I run the Table-valued function.

Thnks again

Marco Dell'Oca

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The names in the IN clause must be static, as they are column names, so they cannot be parameters in the query. They can of course be parameters to something that builds an SQL string.

Basically I run the stored procedure that creates the Table-valued function and then I run the Table-valued function.

This sounds overly complicated to me. Why would you do this? Keep in mind that you would have to recreate the function every time.

Did you look at my primer?

0 Votes 0 ·
MarcoDellOca-1606 avatar image
0 Votes"
MarcoDellOca-1606 answered ErlandSommarskog commented

Yes, I looked at your primer.
Now I explain the reason and the solution I have adopted.
To get a representation in MS Excel, I need to rotate the same data twice.
The first is to list the sum of sales by date on a series of items from large retailers, different locations, brands, shelves, where I have daily sales and daily stock.
The second is to add the daily stock to the sales.
The dates I want to find are 30 days prior to the date I am about to enter.
My solution is as follows:
First I created a view of the data that interests me, where I entered the sales date in two formats, one for sales and one for stock (dd/mm/yyyy and yyyymmdd).
Next I created a stored procedure to which I pass the end date of the 30 days of sale and the parameter IN @ListToPivot, which creates a PivotTable-valued function using the View created initially.
Then I create a new stored procedure that takes as input the end date of the 30 days of sale.
Generate the @ListToPivot of the top 30 in the view using coalesce.
Executes the PivotTable-valued function passeing the date and @ListToPivot.
Builds an SQL string no longer on the initial view but on the table-valued function by passing the date and @ListToPivot.
finally, it performs this SQL string.
If you want I can also pass you all the scripts.

Greetings

Marco Dell'Oca

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is a pivot-capability in Excel...

0 Votes 0 ·