You need to check a few things.

Do you have good indexes that the optimiser can use.

Do you have good statistics that the optimizer can use.

Could post your table definition ,...

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

looked it up in BOL

are you saying that you only want 2 columns return

YEAR ALL_MONTHS(AVG)

2008/09 209

2009/10 117

etc

etc

?

It's basically because the table is returning a row for each column name and building a string by adding a new union the SQL variable for each row returned

that could should work in 2005 as well.

I'm still not 100% sure what the problem is.

Could you show me for example

something like this:

--HOW TO CREATE TABLE

DECLARE @Patient TABLE

(

FinYear VARCHAR(10),...

if have questions regarding how it works let me know...

could you post an example of what you want your output to be?

Here is a solution with no temp tables or table variables.

As mentioned in my other post I found this sometime ago and customized it to my needs:

SET NOCOUNT ON

--SAMPLE...

Have you tried using the system tables in SQL?

Here is a dynamic unpivot solution with not temp tables or table variables.

I must admit that I found this code a while...

are you sure that happycat59's solution above does not work it works for me?

Or you could try this:

DECLARE @sql VARCHAR(200)

SELECT @sql = 'DECLARE @table TABLE

(

col1 CHAR(1)

)

INSERT INTO @table SELECT ''1''

SELECT

*

FROM...

Ok did some more research and asked around (thanks Gail).

It seems that the scan counts are more than what would expect basically because of the degree of Parallelism the Optimizer...

Hi Again,

It seems my research got last so I am going to start again today when I get chance.

The last remember was that with my data if my query plans...

Just a side not for anyone that is interested.

I've created a table on my test system with two columsn

VARCHAR(900) --Max size for a clustered index

VARCHAR(32)

even when I create two index...

cool well when you get a chance, it would be nice to see the table and index definitions along with the query plan.

As mentioned please could you post the query plan, and could you let us know how many rows are in the table or am I right in saying it's just...