/*
exec CrossTab

'select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles

inner join pubs..sales sales on

(sales.title_id=titles.title_id)

inner join pubs..stores stores on sales.stor_id = stores.stor_id ',

'stor_id',

'SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE '''')[MaxStoreName], COUNT(1 ELSE 0)[Count]',

'title',

'Count(*) as TotalCount'
*/



create procedure CrossTab2 (@SQL varchar(1000),

@PivotCol varchar(100),

@Summaries varchar(100),

@GroupBy varchar(100),

@OtherFields varchar(100) = Null,

@Debug bit = 0)

AS



set nocount on

set ansi_warnings off



declare @Vals varchar(8000);

declare @Vals2 varchar(8000);

declare @Vals3 varchar(8000);

declare @tmp varchar(1000);

declare @TotalLen int;



set @Vals = '';

set @Vals2 = '';

set @Vals3 = '';

set @TotalLen = len(@SQL) + len(@GroupBy) + Len(ISNULL(@OtherFields,''))



set @OtherFields = isNull(', ' + @OtherFields ,'')



create table #temp (Pivot varchar(100))



insert into #temp

exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @SQL + ') A')



select @tmp =

replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(Pivot,'''','''''') +

''' THEN '),')[', ' END) as [' + Pivot ),

@TotalLen = @TotalLen + Len(@tmp),

@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,

@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,

@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end

from
#Temp
order by
Pivot



drop table #Temp

if (@Debug=0)

exec ( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +

@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)

else

begin

create table #Temp2 (SQLText Text);

insert into #Temp2 (SQLText)

values ('select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +

@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy);

select * from #Temp2

end


set nocount off
set ansi_warnings on[@more@]