use common
go

go
drop proc transform
go
create proc transform
	@option char(1),
	@type char(1)=null,
	@tsql varchar(255)=null,
	@agg_function varchar(255)=null,
	@agg_expresion varchar(255)=null,
	@Pivot_field varchar(255)=null,
	@Pivot_values varchar(255)=null,
	@pivot_columns int=null,
	@op_table varchar(255)=null,
	@Pivot_order char(2)=null,
	@pivot_remainder varchar(255)='Remainder',
	@debug tinyint=0

AS
SET NOCOUNT ON
set quoted_identifier off
-- -----------------------------------------------------------------------------
--
-- Object Name:		Transform
-- Author:		Aender
-- Created:		04Aug98
--
-- Description:		Transform function to emulate MS/Access Transform statement
--
-- Overview		Multiple calls are required to 'transform'
--			First call with option 'S'tart to clear work tables
--			
--			To allow Select statemnts of greater than 255 characters
--			the Select statment is built up my multiple calls to 'transform' 
--			with the 'W'rite option
--			
--			The 'T'Transform option runs the cross tab.
--				 
-- Parameters:		@option char(1) 
--					S 	'S'tart
--					W 	'W'rite
--					T 	'T'ransform
--			@type char(1) 
--				When building up the select statement the distint parts of the
--				statement should be identifies with @type. This is to make parsing
--				of the statement easier. i.e. It dosn't have to cope with sub-queries.	
--				SEE EXAMPLES.
--					S 	'S'elect
--					F	'F'rom
--					W	'W'here
--					G	'G'roup by
--					H	'H'aving
--
--			The selectstatement of the MS/ACCESS transform statment is built up with multiple
--			@Option	= 'W'rite's
--
--			@tsql varchar(255) 
--				This is part of the SELECT statement. required for option 'W'
--
--			The aggfunction of the MS/ACCESS transform statment is split into 2 parts
--				@agg_function varchar(255) 
--					Sum,Ave,max etc.
--
--				@agg_expresion varchar(255) 
--					Column or expresion that @agg_function works on
--
--			@Pivot_field varchar(255) 
--				The field or expression you want to use to create column headings in the query's result set.
--
--			@Pivot_values varchar(255) 
--				Fixed values used to create column headings.
--				Supplied as rows in a table. The column heading names must be in this table in a column called 'name'
--
--			@op_table varchar(255) 
--				The result of the transform is placed in this table
--
-- ENHANCEMENTS
--			The reason for writing this routine was to display the top ten @pivotfield's ordered by the the highest
--			sum(@pivot_field) first follows by the remainder pooled together in the final column.
--
--			@pivot_columns int 
--				Determines how many @pivotfield values to display.
--
--
--			@Pivot_order char(2) 
--					ND	Order by pivotfield value descending
--					NA	Order by pivotfield value ascending
--					FD	Order by @agg_function(@agg_expresion) descending
--					FA	Order by @agg_function(@agg_expresion) ascending
--			CONFUSED ? - Well look at the code
--
--			@pivot_remainder varchar(255)='Remainder',
--				When you have reduced the number of pivot values by @pivot_columns or supplying
--				@pivot_names. A final column is created to collect all the unselect pivot values.
--
-- ----------------------------------------------------------------------------- 
-- Notes: 
--	1)	This routine makes calls to 'batch_tsql'. 
--		'batch_tsql' runs TSQL that has been bulit up in a table.
--	2)	If you don't supply pivot values the select statement is run twice
--			BEWARE ON BIG QUERIES
-- ----------------------------------------------------------------------------- 
--
-- Return Codes:		0	okay
--				-1	Missing Type when Writing SQL
--  
-- History:
--			Date	Name	Version	Description
--
-- ----------------------------------------------------------------------------- 


CREATE TABLE #tsql (id int identity primary key,tsql varchar(255))

DECLARE	@spid varchar(8)	SELECT @spid=convert(varchar(8),@@spid)
DECLARE @cname varchar(30)	

---------------------------------
-- Start anew
---------------------------------

IF @option='S'
  BEGIN
	DELETE transform_work where spid=@@spid
	RETURN	0
END

---------------------------------
-- Write SQL to work table
---------------------------------

IF @option='W'
  BEGIN
	IF @type=null RETURN -1
	INSERT INTO transform_work (type,tsql) SELECT @type,@tsql
	RETURN	0
END

---------------------------------
-- Obtain column headings
---------------------------------

IF @option<>'T' RETURN -2

EXEC	("DROP TABLE " + @op_table)
IF @pivot_values=null
  BEGIN

	INSERT	INTO #tsql SELECT 'INSERT	INTO common..transform_work (type,freq,spid,tsql) SELECT "C",' +@agg_function +'('+@agg_expresion +'),' +@spid + ',' + @Pivot_field 
	INSERT	INTO #tsql SELECT tsql from transform_work WHERE type IN ('F','W') and spid=@@spid
	INSERT	INTO #tsql SELECT 'GROUP BY ' + @Pivot_field 
	EXEC	batch_tsql #tsql
	IF @debug > 0 SELECT 'Column Generation'=tsql FROM #tsql 
END
 ELSE
	EXEC ("INSERT	INTO common..transform_work (type,spid,tsql) SELECT DISTINCT 'C'," + @spid + ",name from " + @pivot_values )

------------------------------------------
-- Generate the transform select statement
------------------------------------------

TRUNCATE table #tsql 
INSERT	INTO #tsql SELECT "set quoted_identifier on"
INSERT	INTO #tsql SELECT tsql from transform_work WHERE type IN ('S') and spid=@@spid
 
DECLARE @order varchar(50)

IF @Pivot_order <> null	
	SELECT @order=case 
		when @Pivot_order='ND' then 'ORDER BY tsql DESC'
		when @Pivot_order='NA' then 'ORDER BY tsql ASC'
		when @Pivot_order='FD' then 'ORDER BY freq DESC'
		when @Pivot_order='FA' then 'ORDER BY freq ASC'
	END
 
IF @debug>0 SELECT 'orderby'=@order

IF @debug>0 
SELECT 	'Cursor'="
	DECLARE c_cursor INSENSITIVE CURSOR
	FOR	SELECT	tsql
		FROM	transform_work
		WHERE	spid=" + @spid +
	" AND	type='C' " + 
	" AND	tsql <> '" + @pivot_remainder + "' " + 
	@order

EXEC ("
	DECLARE c_cursor INSENSITIVE CURSOR
	FOR	SELECT	tsql
		FROM	transform_work
		WHERE	spid=" + @spid +
	" AND	type='C' " + 
	" AND	tsql <> '" + @pivot_remainder + "' " + 
	@order
	)

DECLARE @n varchar(255), @x varchar(255)

OPEN 	c_cursor
DECLARE	@loop int 	SELECT @loop=1

CREATE TABLE #notin (name varchar(255))
WHILE	@loop <= isnull(@pivot_columns,@loop)
  BEGIN
	FETCH NEXT FROM c_cursor INTO @n  
	IF @@FETCH_STATUS <> 0
		BREAK
 	INSERT	INTO #notin SELECT case When @loop=1 then ' ' else ',' end + "'" + @n  +"'"  
	INSERT	INTO #tsql  SELECT '	,"'+ @n + '"=' + 
			@agg_function + "( case when " + @Pivot_field + "= '" + @n + "' then " + @agg_expresion + " else 0 end )"
	SELECT @loop=@loop+1
END
--	,"B AND H KINGSIZE"=SUM( case when ' + @Pivot_field + '= 'B AND H KINGSIZE' then b.FREQtd else 0 end )   
---------------------------------------------
-- Dump the remaining columns into Remainder
---------------------------------------------

SELECT	@cname=@pivot_remainder
INSERT	INTO #tsql  SELECT '	,"'+ @pivot_remainder + '" = ' + 
		@agg_function + '( case when ' + @Pivot_field + ' NOT IN ('
INSERT	INTO #tsql  SELECT	'	' + name FROM #notin
INSERT	INTO #tsql  SELECT '	) then ' + @agg_expresion + ' else 0 end )' 

DEALLOCATE c_cursor

------------------------------------------
-- Tag on INTO and FROM WHERE and GROUP BY
------------------------------------------

INSERT	INTO #tsql	SELECT 'INTO	' + @op_table
INSERT	INTO #tsql  	SELECT tsql from transform_work WHERE type IN ('F','W','G') and spid=@@spid
EXEC	batch_tsql #tsql

IF @debug > 0 SELECT 'Cross tab Statement'=tsql FROM #tsql 
IF @debug > 0 select tsql from transform_work where spid=@@spid


-----------------------------------------------------------------------------------------------------------------------------------------

GO