Saturday, March 05, 2011

za_trsnsform

/****** Object: StoredProcedure [dbo].[za_transform] Script Date: 03/03/2011 23:20:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:
-- Create date: <3/1/2011>
-- Description:
--
-- Version 1.0
-- Published (temporarily) under LGPL 3.0
-- Next versions may switch to another open source license
--
-- Simpliistic implemetation
-- Usage:
-- EXEC @return_value = [dbo].[za_transform]
-- @Aggregate_Function = aggregate function (e.g. N'sum'), [default sum]
-- @Aggregate_Column = column to aggregate using the funcion,
-- @TableOrView_Name = table name or select query. Must contain the pivot column,
-- @Select_Column = row header columns, [PIVOT allows more then one]
-- @Pivot_Column = the field to pivot on,
-- @IN nvarchar = default NULL - Fixed values used to create column headings
-- THIS OPTION IS NOT YET IMPLEMENTED,
-- @DEBUG = 0/1 (currently default 1) print the select/pivot statement
--
-- This implemetation use a temporary table #_ZAT_pivotNames that is dropped in the
-- end of the run.
-- This version implements partial and very simplistic error checking.
-- Note that the user interface for this sproc was made to be compatible
-- with the popular sp_transform. New parameters are added in the end of
-- the parameter list, except of flags such as DEBUG.
-- This sproc was designed to use new (2005, 2008, etc.) features in SQL
-- server such as FOR XML PATH and PIVOT, but the autor is still full of
-- owe for the achivement of sp_transform and what could be done with
-- the primitive tools available in 2000.
--
-- =============================================
ALTER PROCEDURE [dbo].[za_transform]
@Aggregate_Function nvarchar (2000),
@Aggregate_Column nvarchar (2000),
@TableOrView_Name nvarchar (2000),
@Select_Column nvarchar (2000) = null,
@Pivot_Column nvarchar (2000),
@IN nvarchar(2000) = null,
@DEBUG bit = 1
AS
BEGIN
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#_ZAT_pivotNames','u') IS NOT NULL
drop table #_ZAT_pivotNames;

declare @tableName nvarchar(2000);
declare @PivotColumnName nvarchar(2000);
declare @aggregate nvarchar(2000);
declare @rowHeader nvarchar(2000);
declare @inList nvarchar (2000); --< develop this idea
declare @return int = 0; --< develop this idea
-- Develop total option.
declare @SQLSTR nvarchar (max);

declare @i int;

IF @Aggregate_Function NOT IN
('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN
RAISERROR ('Invalid aggregate function: %s', 0, 1, @Aggregate_Function)
set @return = -1;
goto droptab
END

create table #_ZAT_pivotNames (pivotName sql_variant);

set @tableName = @TableOrView_Name
set @PivotColumnName = @Pivot_Column
set @aggregate = @Aggregate_Function + '(' + @Aggregate_Column + ')'
if @Select_Column is not null
set @rowHeader = @Select_Column + ',' + @PivotColumnName + ',' +
@Aggregate_Column
else
set @rowHeader = '*'
if ltrim (@tableName) like 'select %'
begin
set @tableName = '(' + @tableName + ') a'
end

set @tableName = '(select ' + @rowHeader +' from ' + @tableName + ') b'

set @tableName = '(select * from ' + @tableName + ')'

set @SQLSTR = 'insert into #_ZAT_pivotNames ' +
'select distinct ' + @PivotColumnName +
' from ' + @tableName + ' a;'

exec @i = sp_executesql @SQLSTR
if @i <> 0
begin
RAISERROR ('Invalid return code from sp_executesql: %d5', 0, 1, @i)
set @return = -1;
goto droptab
end

select @i = count (*) from #_ZAT_pivotNames
where cast(pivotName as nvarchar(2000)) like '%\[%' escape '\'
or cast(pivotName as nvarchar(2000)) like '%\]%' escape '\';

-- I may use QUOTENAME with its limitations or replace all ] by ]]
if coalesce(@i,0) <> 0
BEGIN
RAISERROR ('Invalid pivot column value: [ or ]', 0, 1)
set @return = -1;
goto droptab
END
else
begin
set @SQLSTR = (SELECT '[' + cast (pivotName as nvarchar(2000)) + '],'
FROM #_ZAT_pivotNames
FOR xml PATH(''));

set @SQLSTR = left (@SQLSTR, len(@SQLSTR) -1)

set @SQLSTR = 'SELECT * from ' + @tableName +
' a PIVOT (' + @aggregate + ' FOR ' + @PivotColumnName + ' in ('
+ @SQLSTR +
')) as p;'

if @DEBUG = 1
print @SQLSTR;

exec @i = sp_executesql @SQLSTR

if @i <> 0
begin
RAISERROR ('Invalid return code from sp_executesql: %d5', 0, 2, @i)
set @return = -1;
goto droptab
end
end

droptab:
drop table #_ZAT_pivotNames;
return @return;

END



GO

posted by ZA @ 11:43 PM 0 comments