create procedure CrossTab (@Select varchar(1000), 
    @PivotCol varchar(100),
    @Summaries varchar(100), 
    @GroupBy varchar(100),
    @OtherCols varchar(100) = Null)
AS

set nocount on
set ansi_warnings off

DECLARE @Select varchar(1000), 
        @PivotCol varchar(100),
        @Summaries varchar(100), 
        @GroupBy varchar(100),
        @OtherCols varchar(100)
SELECT 
    @Select = 'SELECT buildexcludereason, Buildprojectcs FROM (select buildprojectcs, buildexcludereason 
from dialermasterrawfilehist where datadate = ''9/05/07'' and owner in (''p1'',''p2'') 
and buildexcludereason is not null) mytest',
    @PivotCol = 'buildexcludereason',
    @Summaries = 'Count(buildexcludereason)[]',
    @GroupBy = 'buildprojectcs',
    @OtherCols = ''
--    'buildprojectcs'  -- This one seems to be an extra parameter, so you'll need to sort this out!

declare @Vals varchar(8000);
set @Vals = '';
 
set @OtherCols= isNull(', ' + @OtherCols,'')
 
create table #temp  (Pivot varchar(100))
 
insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @Select + ') A')
 
select @Vals = @Vals + ', ' + 
    replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + 
            Pivot +  ''' THEN '),')[', ' END) as [' + Pivot )
from #Temp 
order by Pivot

drop table #Temp

exec ( 'select ' + @GroupBy + @OtherCols + @Vals + 
       ' from (' + @Select + ') A GROUP BY ' + @GroupBy)
 
set nocount off
set ansi_warnings on

quote:

---

Yes... forget doing the pivot in SQL... import the data into Excel and use the Excel pivot to warp the data.

--Jeff Moden

---

quote:

---

Might depend on how many rows there are in the query/ (I think Excel has a limit of 32,000 rows??)

Can't you set up a Database Query in Excel as raw SQL? In which case you can just paste in your working SQL. But it would be easier if you could create an Sproc in the database

---