"PIVOT" function or Analysis Service for SQL-Server 2000

  • Hi!  I am relatively new to SS.

    I was in a company that had SS2005, but now I am where SS2K is my only option. I have worked with MS-Access and MS-Excel for many years.

    I have a task where I need to crunch a lot of numbers, but present them like a MS-Access or MS-Excel "Pivot Table". I know that SS2005 had a T-SQL "PIVOT" function, but I don't think S2K does. I know that SS2005 has an Analysis Service that allowed creating hypercubes but I did not have the opportunity to learn about it or use it.

    My situation: 

    I have multiple potential categories for "row headers" (more than 5 and they change). I have one category for "column headers" (26 Pay Periods) but not all of them have data until the end of the year. I need to be able to put together the data in a flexible way that does not require constant re-coding.

    Questions:

       1) Does anyone know of a T-SQL function (or method) to pivot my data in SS2K that does not require re-coding when the Row or Column categories change?

       2) Is there an "analysis-like" feature in the standard SS2K version that would allow this type functionality? (using a 3rd party OLAP tool is not an option)

       3) Can you point me to something on the web that would give me a head start on how to do this?

    Many thanks from a newbie! 

  • search this site for pivot or crosstab query for examples.  This topic comes up a lot.  Here is a proc I use in SQL7 and 2K.  I'll get blasted for using cursors but in my case the performance hit isn't a concern.

     

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS OFF

    GO

     

    ALTER     PROCEDURE [createpivot] 

    @dbname varchar(8000),

    @pivotrows varchar(8000),

    @pivottable varchar(8000),

    @pivotdata varchar(8000),

    @pivotcols varchar(8000),

    @pivotfunc varchar(8000)

    as declare

    @sqltxt varchar(8000),

    @sqlstmt varchar(8000),

    @pivotcolumn varchar(8000)

    /*set pivotrow names here comma delimited

    set @dbname = 'WEB_Reports'

    set @pivotrows = 'tylddate'

    set @pivottable = 'vw_dlycntsumbyleadtype'

    set @pivotdata = 'tyldcount'

    set @pivotcols = 'lead_type'

    set @pivotfunc = 'sum'

    do not edit below this line*/

    exec('use ' + @dbname)

    set @sqltxt = 'select '+@pivotrows+','

    set @sqlstmt = 'declare pivot_cursor cursor for select distinct('+@pivotcols+') from '+@pivottable

    exec(@sqlstmt)

    open pivot_cursor

    fetch next from pivot_cursor

    into @pivotcolumn

    WHILE @@FETCH_STATUS = 0

    BEGIN

       -- This is executed as long as the previous fetch succeeds.

    set @sqltxt = @sqltxt + ' ' + @pivotfunc + '(case '+ @pivotcols +' when '''+ @pivotcolumn + ''' then '+ @pivotdata +' else 0 end) as ['+ @pivotcolumn +'],'

       FETCH NEXT FROM pivot_cursor

       into @pivotcolumn

    END

    CLOSE pivot_cursor

    DEALLOCATE pivot_cursor

    set @sqltxt = left(@sqltxt,len(@sqltxt)-1)

    set @sqltxt = @sqltxt + 'from ' + @pivottable + ' group by ' + @pivotrows + ' order by ' + @pivotrows

    /*print @sqltxt*/

    exec(@sqltxt)

     

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

     


  • That's why the RAC utility exists. It wil do everything a crosstab utility should do and more. If you know the Access crosstab you already know what RAC is doing with sql server.

    http://www.rac4sql.net

    http://www.beyondsql.blogspot.com

     

     

  • Rog,

    Where do I find info on this "RAC" utility. Is it 3rd party?

     

    Thanks,

     

  • Yes.

    RAC @

    http://www.rac4sql.net

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply