/*
in order to evaluate all the profiles in t_profile_column_values,
we need to restructure them as rows of the form
id, col1, col2, . . . coln
where colN is either
NUll - column doesn;t matter
"==:N" - mine relation column values = N
"!=:N" - mine relation column values != N
We generate the create function statement that performs
this transformation.
*/
drop procedure up_generate_profile_table_function
go
create procedure up_generate_profile_table_function
as
declare
   @sql_initial varchar(8000),
   @sql varchar(8000),
   @next varchar(30),
   @columns_done int
begin
select *
into #t1
from v_columns
order by colorder
--select * from #t1

select @sql = 'drop function uf_generated_profile_table'
exec(@sql)
select @sql = ''
select @sql = @sql + 
'create function uf_generated_profile_table() ' + char(13) + char(10)
select @sql = @sql + 'returns table ' + char(13) + char(10)
select @sql = @sql + 'as return (' + char(13) + char(10)
select @sql = @sql + 'select p.id,' + char(13) + char(10)

select @columns_done = 0
while ( exists (select * from  #t1))
begin
    select top 1 @next = name
    from #t1

    select  @sql = @sql + @next +  
' = (SELECT operator + '':'' + c_value FROM t_profile_column_values ' 
+   'WHERE c_name=''' + @next + ''' and id = p.id)'

    delete from #t1 where name = @next
    if exists (select * from  #t1)
        select  @sql = @sql + ',' + char(13) +char(10)
    else
        select  @sql = @sql + char(13) +char(10)

select @columns_done = @columns_done + 1
if ((@columns_done % 50) = 0)
   begin
     select @sql_initial = @sql
     select @sql = ''
   end

end

select @sql = @sql + 
' from t_profile_column_values p' + char(13) +char(10) 
select @sql = @sql + ' group by id )' + char(13) +char(10) 
--select @sql_initial, @sql
exec(@sql_initial + @sql)
end
go
/*
Create the function above.
*/
exec up_generate_profile_table_function
go
