auto pivot colums sp as Table-valued function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

auto pivot colums sp as Table-valued function

Author

Topic

davidhills
Starting Member

14 Posts

Posted - 2010-11-29 : 09:19:18

Good Morning
is it possible to rewrite this sp as a Table-valued function

if so , can I get some help

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[pivot_sp] Script Date: 11/29/2010 09:30:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROC [dbo].[pivot_sp1]
@query AS NVARCHAR(MAX), -- The query, can also be the name of a table/view.
@on_rows AS NVARCHAR(MAX), -- The columns that will be regular rows.
@on_cols AS NVARCHAR(MAX), -- The columns that are to be pivoted.
@agg_func AS NVARCHAR(257) = N'MAX', -- Aggregate function.
@agg_col AS NVARCHAR(MAX), -- Column to aggregate.
@debug AS bit = 1
AS

DECLARE
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);

SET @newline = NCHAR(13) + NCHAR(10);

SET @query = N'(' + @query + N') AS Query';

-- Construct column list
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + quotename( '
+ 'CAST(pivot_col AS sysname)' +
+ ') AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col' + @newline +
N' FROM' + @query + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH(''''))' + @newline +
N' ,1, 1, N'''');'

--IF @debug = 1
--PRINT @sql

EXEC sp_executesql @stmt = @sql,@params = N'@result AS NVARCHAR(MAX) OUTPUT',@result = @cols OUTPUT;

--select @sql as xx;

--Create the PIVOT query
SET @sql =
N'SELECT *' + @newline +
N'FROM (SELECT '
+ @on_rows
+ N', ' + @on_cols + N' AS pivot_col'
+ N', ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @query + N')' +
+ N' AS PivotInput' + @newline +
N' PIVOT(' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'

EXEC sp_executesql @sql;

RETURN;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 13:17:18

not without dropping the dynamic part. Whats the purpose of making this a table valued fn?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources