CODEWITHSUNDEEP
sqldynamicpivot
Jan de Jager
Jan de Jager

Reputation: 870

SQL Function that returns dynamically pivotted data

Is it at all possible to have a table valued function in MSSQL that takes an attribute and generates an associated SQL statement with 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

Upvotes: 7

Views: 17929

Answers (4)

MgSam
MgSam

Reputation: 12803

I've made the following procedure for dynamically pivoting 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

Usage:

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

Upvotes: 1

Brandon J. Boone
Brandon J. Boone

Reputation: 16472

Here's something I did to accommodate this kind of feature (as a view, but a function would follow the same methodology). We stuck it at the end of every install script so when any changes were made to the table being pivoted, they would be picked up in the re-generated 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]

Upvotes: 2

cjk
cjk

Reputation: 46425

A table valued function must always return a specified schema, so this isn't possible.

Upvotes: 3

Joel Mansford
Joel Mansford

Reputation: 1316

Unfortunately not, with the exception of stored procedures SQL Server keeps track of the table definition of the output of all views, functions etc. Thus the columns and types can't change dynamically on the input.

For this reason I've never found the PIVOT operator to be useful. Generally the only way to get varying column data out is to treat it as XML.

For what reason are you pivoting it? This is usually a UI concern, and so I would recommend doing it at the UI, or SSRS etc.

Upvotes: 6

Related Questions