use sybsystemprocs
go

CREATE PROCEDURE sp__create_crosstab
        ,@code_table    varchar(30)     -- table containing code lookup rows
        ,@code_key_col  varchar(30)     -- name of code/lookup ID column
        ,@code_desc_col varchar(30)     -- name of code/lookup descriptive text column
        ,@value_table   varchar(30)     -- name of table containing detail rows
        ,@value_col     varchar(30)     -- name of value column in detail table
        ,@value_group_by varchar(30)    -- value table column to group by.
        ,@value_aggregate varchar(5)    -- operator to apply to value being aggregated

AS
/*
Copyright (c) 1997, Clayton Groom. All rights reserved.
Procedure to generate a cross tab query script
Reqires:
        1. A lookup table with a code/id column and/or descriptive text column
        2. A data table with a foreign key from the lookup table & a data value to aggregate
        3. column(s) name from data table to group by
        4. Name of an aggregate function to perform on the data value column.
*/

set nocount on

if sign(charindex(upper(@value_aggregate), 'MAX MIN AVG SUM COUNT')) = 0
BEGIN
        print "@value_aggregate value is not a valid aggregate function"
        -- return -1
END

declare @value_col_type         varchar(12)     -- find out data type for aggregated column.
        ,@value_col_len         int             -- get length of the value column
        ,@str_eval_char         varchar(255)
        ,@str_eval_int          varchar(255)
-- constants
        ,@IS_CHAR               varchar(100)    -- character data types
        ,@IS_NOT_ALLOWED        varchar(100)    -- data types not allowed
        ,@IS_NUMERIC            varchar(255)    -- numeric data type names
        ,@NL                    char(2)         -- new line
        ,@QUOTE                 char(1)         -- ascii character 34 '"'
--test variables
        ,@value_col_is_char     tinyint         -- 1 = string data type, 0 = numeric or not allowed
        ,@value_col_is_ok       tinyint         -- 1 = string or numeric type, 0 = type cannot be used.
        ,@value_col_is_num      tinyint         -- 1 = numeric data type, 0 = string or not allowed

select   @IS_CHAR       = 'varchar char nchar nvarchar text sysname'
        ,@IS_NOT_ALLOWED= 'binary bit varbinary smalldatetime datetime datetimn image timestamp'
        ,@IS_NUMERIC    = 'decimal decimaln float floatn int intn money moneyn numeric numericn real smallint smallmoney tinyint'
        ,@NL            = char(13) + char(10)
        ,@QUOTE         = '"'   -- ascii 34

-- get the base data type & length of the value column. Is it a numeric type or a string type?
-- need to know this to use string or numeric functions in the generated select statement.
select  @value_col_type = st.name
        ,@value_col_len = sc.length
                        from syscolumns sc
                                ,systypes st
                        where   sc.id = object_id(@value_table)
                        and     sc.name = @value_col
                        and     sc.type = st.type
                        and     st.usertype = (select min(usertype)
                                                from systypes st2
                                                where st2.type = sc.type)
--select @value_col_type, @value_col_len

select @value_col_is_char = sign(charindex( @value_col_type, @IS_CHAR))
        ,@value_col_is_ok = 1 - sign(charindex( @value_col_type, @IS_NOT_ALLOWED))
        ,@value_col_is_num = sign(charindex( @value_col_type, @IS_NUMERIC))

IF @value_col_is_ok = 1
BEGIN
        if @value_col_is_char = 1
        begin
                select @str_eval_char = ''
        end
        else
        if @value_col_is_num = 1
        begin
                select @str_eval_char = ''
        end
        else
        begin
                print " @value_col data type unnown. must be string or numeric"
                -- return -1
        end
END
ELSE    --ERROR
BEGIN
        print " @value_col data type not allowed. must be string or numeric"
        -- return -1
END

-- template. first level expansion query.
-- result must be executed to generate final output query.

SELECT "select 'select " + @value_group_by + "'"
IF @value_col_is_char = 1
BEGIN
        SELECT "select '," + @QUOTE + "' + convert(varchar(40), " + @code_desc_col+ " ) + '" + @QUOTE + @NL
        + " = "
        + @value_aggregate
        + "(isnull( substring("
        + @value_col
        + ", 1, ( "
        + convert(varchar(3), @value_col_len )
        + " * charindex( "
        + @QUOTE
        + "'+"
        + @code_key_col
        + "+'"
        + @QUOTE
        + ", "
        + @code_key_col
        + " ))), "
        + @QUOTE + @QUOTE
        + "))'"
END
ELSE IF @value_col_is_num = 1
BEGIN
        SELECT "select '," + @QUOTE + "' + convert(varchar(40), " + @code_desc_col+ " ) + '" + @QUOTE + @NL
        + " = "
        + @value_aggregate
        + "("
        + @value_col
        + " * charindex( "
        + @QUOTE
        + "'+"
        + @code_key_col
        + "+'"
        + @QUOTE
        + ", "
        + @code_key_col
        + "))'"
END
SELECT "from " + @code_table + @NL
        + "select  'from " + @value_table + "'" + @NL
        + "select 'group by " + @value_group_by + "'"

-- end
go
