SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO


/*====================================================================================
							Renaissance Computer Systems LTD.
======================================================================================
By Kim Major (07/07/1999) for IVBUG (Israeli Visual Basic User Group)
kim@renaissance.co.il

Purpose: Create dynamic cross tab query. The result of this stored procedure, is a select statement
that generates a crosstab query based on the input parameters. The result of this query as well as 
the select statements itself are returned to the client in two resultsets.

Sample call in the northwind database (requires custom view)
execute sp_Cross 'QuarterlyOrdersByProduct', 'Quarters','ProductName' , 'ProductAmount'

Sample call with expression for column in the northwind database (requires custom view)
execute sp_Cross 'TableName', 'DATEPART(month, ColName)','ColName' , 'ColName'

This sp may be used in your application without permission as long as the 
header above these two lines is not removed. 

This sp may not be made public on a web site or other media without permission.

====================================================================================*/

create procedure sp_Cross
	@tablename			varchar(255),	-- Table/View on which to perform the cross tab query.
	@crosscolumn		varchar(255),	-- Attribute to be used as columns in the cross tab.
	@crossrow			varchar(255),	-- Attribute to be used as rows in the cross tab.
	@crossvalue			varchar(255)	-- Attribute to be used as value in the cross tab.

As

-- Work variables
declare 
	@sql				varchar(8000),	-- Hold the dynamically created sql statement
	@colname			varchar(255),	-- The current column when building sql statement
	@i					smallint,		-- know when we reached the last column (@i = @cols)
	@cols				smallint,		-- Number of columns
	@longest_col		smallint,		-- the len() of the widest column
	@CrLf				char(2)
-- Constants
declare 
	@max_cols_in_table	smallint,
	@max_col_name_len	smallint,
	@max_statement_len	smallint,
	@sql7				bit,			-- 1 when version 7, 0 otherwise.
	@err_severity		int

set nocount on

set @max_cols_in_table	= 255
set @max_statement_len	= 8000
set @max_col_name_len	= 128
set @err_severity		= 11
set @CrLf = char(13) + char(10)

-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
	raiserror ('Missing parameter(s)!',@err_severity,1)
	return 0
end

-- Check for existence of the table.
if (not exists(select * from sysobjects where name like @tablename))begin
	raiserror ('Table/View for crosstab not found!',@err_severity,1)
	return 0
end

-- Don't check for columns because we may actually get an expression as the column name

-- prepare for future feature of checking database version to validate 
-- inputs. Default to version 7
set @sql7 = 1
if (patindex('%SQL Server  7.%',@@version) = 0) begin 
	set @sql7 = 0
end

-- Extract all values from the rows of the attribute 
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql =	' insert #crosscol Select Distinct ' + @crosscolumn + 
			' From ' + @tablename --+ 
			--' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount

if @cols > @max_cols_in_table begin
	raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1) 
	return 0
end
else begin
	if @cols = 0 begin
		raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
		return 0
	end
	else begin
		-- Check if any of the data is too long to make it a name of a column
		select @longest_col = max(len(convert(varchar(129),crosscolumn))) 
		from #crosscol 

		if @longest_col > @max_col_name_len begin
			raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
			return 0
		end
		else begin
			
			-- All Validations OK, start building the dynamic sql statement

			set @sql = ''
			-- Use tmp table rows to create the sql statement for the crosstab.
			-- each row in the table will be a column in the cross-tab
			set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As ' 
						+ @crossrow + ', ' + @CrLf + space(4)

			--set @sql = 'select ' + @crossrow + ', ' + char(13)
			
			declare cross_sql cursor for
			select crosscolumn 
			from #crosscol 
			order by crosscolumn

			--print 'Sql cross statment: ' +  @sql

			open cross_sql
			fetch next from cross_sql into @colname
			-- Use "@i" to check for the last column. We need to input commas 
			-- between columns, but not after the last column
			set @i = 0
			while @@FETCH_STATUS = 0 begin
				set @i = @i + 1
				set @colname = isnull(@colname,'Undefined')
				set @crossvalue = isnull(@crossvalue, 0)

				Set @sql = @sql  + '''' +  
					convert(varchar(128), @colname) + 
					''' = sum(case convert(varchar(128), ' + @crosscolumn + ')' 
						+ char(13) + char(10) + space(8) + 
						' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '

				if @i < @cols
					set @sql = @sql + ', ' + @CrLf + space(4)
				else
					set @sql = @sql + @CrLf

				fetch next from cross_sql into @colname
				end

			close cross_sql
			deallocate cross_sql

			set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
			
			if len(@sql) >= @max_statement_len begin
				raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
				return 0
			end

			exec (@sql)
			Select 'Sql' =  @sql
			set nocount off			
			return 1

		end
	end
end













GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO