Dynamic matrix/crosstab using SQL

There's a less costly way of creating a summary on a web page or displayed through another data bound tool for what could be deemed a 'crosstab'. This kind of functionality is built into tools such as Business Objects or Crystal Reports (old name), but what if you wanted to do it in a stored procedure, without using expensive cursors and display it back using simple Servlets/JSP/JSF. I've used this method for quite some time. It involves using `execute(<sql command>)` within a stored proc that you build based on selects that you would normally reserve for a cursor. Here's some pseudo-code with explanations:

create proc summary as

declare @column1 varchar(30)

select column1, processed=0 into #results from main_table where ......

while exists(select 1 from #results where processed = 0)

        begin

        set rowcount 1

       select @column1 = column1 from #results where processed = 0

       set rowcount 0



Contact: daquijne@josephdaqui.com (copyright 2010)