SQL function that returns dynamically dependent data

Question

Is it even possible to have a table evaluation function in MSSQL that takes an attribute and generates a related SQL statement using the Pivot function?

CREATE FUNCTION dbo.fnPivot (@EntityTypeID int) RETURNS TABLE AS BEGIN DECLARE @SQL varchar(MAX); DECLARE @COLS varchar(MAX); select @COLS=coalesce(@COLS+',','')+'['+Name+']'from c_EntityAttribute WHERE EntityTypeID = @EntityTypeID; SET @SQL = 'SELECT * FROM (SELECT EntityInstanceID, AttributeName, Value FROM v_EntityElementData WHERE EntityTypeID = 1) as s'; SET @SQL = @SQL + 'PIVOT ( MIN(Value) FOR AttributeName IN (' + @COLS + ') ) AS p'; RETURN EXEC sp_ExecuteSQL @SQL ; END 

Answer 1

Unfortunately not, except for stored procedures. SQL Server monitors the definition of the output table for all views, functions, etc. Thus, columns and types cannot dynamically change at the input.

For this reason, I have never found the PIVOT operator useful. Generally, the only way to get variable column data is to treat it as XML.

For what reason are you turning it? This is usually a problem with the user interface, so I would recommend doing this in the user interface or SSRS, etc.

Answer 2

A table-valued function should always return the specified schema, so this is not possible.

Answer 3

Here, I did something to place this function (as a representation, but the function will follow the same methodology). We are stuck at the end of each installation of the script, so when any changes to the pivot table were made, they will be selected in the updated view.

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = Object_id(N'[dbo].[vwYourView]') AND type IN ( N'V' )) BEGIN DROP VIEW [dbo].[vwYourView] END GO Declare @cols VARCHAR(MAX) Select @cols = COALESCE(@cols + ',[' + YourColumn+ ']', '[' + YourColumn+ ']') FROM YourTable Order By YourColumn DECLARE @query VARCHAR(MAX) Set @query = N' Create View vwYourView AS Select * FROM YourTable Pivot ( MAX(YourVal) FOR YourColumn IN( '+ @cols +') ) AS pvt ' Execute(@query) Select * FROM [vwYourView] 

Answer 4

I performed the following procedure to dynamically rotate all the values ​​in a table.

 CREATE OR ALTER PROC dbo.spDynamicPivot ( @Query NVARCHAR(MAX) --The query to pivot , @AggregateFunction NVARCHAR(MAX) --The aggregation function surrounding the column name to be pivoted , @ValueColumn NVARCHAR(MAX) --The value column from which to create columns , @ResultTable NVARCHAR(MAX) = NULL --An optional table name into which the results will be stored. Note, this unfortunately will not work with #temp tables. ) AS BEGIN SET XACT_ABORT ON; SET NOCOUNT ON; DECLARE @cols NVARCHAR(MAX); DECLARE @sql NVARCHAR(MAX) = 'SELECT @cols = ISNULL(@cols + '','', '''') + ''['' + Val + '']'' FROM (SELECT DISTINCT Val = ' + @ValueColumn + ' FROM (' + @Query + ') a) b;'; DECLARE @paramDef NVARCHAR(MAX) = '@cols NVARCHAR(MAX) OUTPUT'; EXEC sp_executesql @sql, @paramDef, @cols = @cols OUTPUT; DECLARE @resultSetSql NVARCHAR(MAX) = IIF(ISNULL(@ResultTable, '') <> '', 'INTO ' + @ResultTable + ' ', ''); SET @sql = 'SELECT * ' + @resultSetSql + 'FROM (' + @Query + ') q PIVOT( ' + @AggregateFunction + ' FOR ' + @ValueColumn + ' IN (' + @cols + ') ) p'; EXEC sp_executesql @sql; END 

Using:

 DROP TABLE IF EXISTS #t; CREATE TABLE #t (Name VARCHAR(50), Age INT, NetWorth FLOAT); INSERT INTO #t VALUES ('Bill', 50, 250), ('Barb', 17, 15), ('Fred', 25, 30), ('Bill', 25, 100), ('Kahless', 90000, 4E10) --Displaying results directly EXEC dbo.spDynamicPivot @Query = 'SELECT * FROM #t', @AggregateFunction = 'AVG(NetWorth)', @ValueColumn = 'Name' --Or writing them into a table for additional use DROP TABLE IF EXISTS tempdb.dbo.MyTable; EXEC dbo.spDynamicPivot @Query = 'SELECT * FROM #t', @AggregateFunction = 'AVG(NetWorth)', @ValueColumn = 'Name', @ResultTable = 'tempdb.dbo.MyTable' SELECT * FROM tempdb.dbo.MyTable