za_transform (similar to sp_transform for SQL Serv

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

General SQL Server Forums

Script Library

za_transform (similar to sp_transform for SQL Serv

Author

Topic

zatlas1
Starting Member

3 Posts

Posted - 2011-03-06 : 12:21:18

I've developed a (first) version of za_transform, to create MS-Access like cross-tabs in SQL Server. The user interface of my stored procedure (API) was kept compatible with the venerable (and genius) sp_transform. The result look like the result of using PIVOT in SQL Server and are NOT fully compatible with the results of sp_transform. I wrote this as a proof of concept and herein release it to the public for review, comments, suggestions, corrections and improvements.

My version uses a temporary table since I could not find a way to use a table variable in a mix of static and dynamic SQL. It uses features that were not available in SQL Server 2k such as XML PATH FOR and PIVOT. Therefore it is not compatible with SQL Server 2k and will only work on SQL Server 200, 2008 and beyond.

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

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Ze'ev AStlas>
-- Create date: <3/1/2011>
-- Description: <implementation of MS Access Transform-like command>
--
-- 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

zatlas1
Starting Member

3 Posts

Posted - 2011-03-08 : 17:20:48

Slightly improved version follows. This version will work properly in SQL Server 2005. It also checks for various null parameters.
I have to make sure to prevent any SQL injection attacks!
Also, I plan an option for 'total' row and to include 'in' clause like MS-Access transforem.
Please come with suggestions.

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

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Ze'ev AStlas>
-- Create date: <3/1/2011>
-- Description: <implementation of MS Access Transform-like command>
--
-- Version 1.01
-- 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; --< develop this idea
-- Develop total option.
declare @SQLSTR nvarchar (max);

declare @i int;

set @return = 0;

IF @Aggregate_Function is not null
and @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

if @Aggregate_Function is null
begin
set @aggregate = 'SUM'
end
else
begin
set @aggregate = @Aggregate_Function
end

if @Aggregate_Column is not null
begin
set @aggregate = @aggregate + '(' + @Aggregate_Column + ')'
end
else
begin
RAISERROR ('Aggregate_Column cannot be null', 0, 1)
set @return = -1;
goto droptab
end

if @Pivot_Column is null
begin
RAISERROR ('Pivot_Column cannot be null', 0, 1)
set @return = -1;
goto droptab
end

if @Select_Column is not null
begin
set @rowHeader = @Select_Column + ',' + @PivotColumnName +
',' + @Aggregate_Column
end
else
begin
set @rowHeader = '*'
end
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)

if @tableName is null print 'tableName'
if @aggregate is null print 'aggregate'
if @PivotColumnName is null print 'PivotColumnName'
if @SQLSTR is null print 'SQLSTR'

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 04:11:03

Can you please add [ code ] tags around you sql code and also include usage examples with their corresponding output? I, and probably many like me, are not familiar with the Access like cross-tabs and/or sp_transform.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

zatlas1
Starting Member

3 Posts

Posted - 2011-03-09 : 22:46:23

When preparing an answer, I've found a major deficiency. I will answer when I correct the thing. In essence, I cannot do a function as the pivot column.
I will provide usage and brief explanation of cross-tab when I am done.
ZA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources