"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.beyondsql.blogspot.com
-
Rog,
Where do I find info on this "RAC" utility. Is it 3rd party?
Thanks,
-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply