PIVOT statement whitout knowing values

Just a small issue...

I'm trying the new SQL 2005 (Express) because the PIVOT function was finally added.

I've a table with three columns ID, Height and Width

Now I'd like to have a table with for each height the number of ID for each Width

The easiest way is to use the PIVOT statement.....but..... to use it in SQL2005 I should use:

SELECT Height, [100] AS Width01, [200] AS Width02
FROM (

SELECT ID, Height, Width FROM TestTable) p

PIVOT ( COUNT (ID) FOR Width IN([100], [200]) ) AS pvt

This kind of querry works perfectly in a static situation, but if I add new record in the table referencing the "300" Width to obtain the correct result I have to modify the query.

Is there an options or a technique for having the list of the Width dinamically filled according the table contents.

Thank you very much to anyone how can help me

H

You have to use dynamic SQL to execute the SELECT statement after generating the values for the IN list. There is no other way using static SQL code.|||

To be clear, there are good reasons for this restriction.

SQL Server's PIVOT can exist anywhere in the query tree (unlike in Access), supports UNPIVOT (unlike Access), and does not require recompilation for each execution (unlike Access). These are good things for complex queries, as compilation time would be significantly worse if these did not exist.

SQL Server's query optimizer has a requirement that the column list be known before compilation begins. This allows faster compiles because we can identify duplicate alternatives more easily and avoid doing extra work during compilation. This also helps us to determine if we can avoid searching portions of the possible plan space that obviously will not help find a faster plan than what has been found so far during optimization.

I understand the desire to not have to bother specifying a column list, and perhaps that is something we can add in a future release. The reasons above are reasons it was not added in SQL 2005. Even if such a feature were added, it would be likely better if you could specify a column list to speed system throughput.

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

Thank you all for the clear answer, now I understood that the restriction is due to performances.

Of course this type of restriction have very few impact over small databases like the ones I working on (~100 MB). So I will keep my application over access where the power of the TRANSFORM-PIVOT scheme will help me reducing the programming effort.

Thanks again

H