Is this poor dynamic crosstab code ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is this poor dynamic crosstab code ?

Author

Topic

fisab68
Starting Member

1 Post

Posted - 2005-03-21 : 04:57:57

I've come up with a way of generating a crosstab that I can apply a filter to. It uses a cursor to build up an SQL statement.
Is this a poor way to do it ?
Example of it using the Northwind DB :-

Declare
@year nvarchar(5),
@sql nvarchar(1000)

set @sql = 'SELECT MONTH(OrderDate) AS OrderMonth'

declare den_name CURSOR for
select distinct year(orderDate) from orders

open den_name

fetch next from den_name into @year

while @@fetch_status = 0
begin

set @sql = @sql + ',SUM(CASE YEAR(OrderDate) WHEN ' + @year + ' THEN 1 ELSE 0 END) AS c' + @year

fetch next from den_name into @year
end

close den_name
deallocate den_name

set @sql = @sql + ' FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)'

exec sp_executesql @sql

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-21 : 05:02:28

yes it is bad

check this out:
http://www.sqlteam.com/item.asp?ItemID=2955

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources