|
|
Posted by Erland Sommarskog on 05/28/06 15:11
(billmiami2@netscape.net) writes:
> One thing that disappointed me was the PIVOT function. It seems that
> the column names must be hard coded. I suppose that I can get around
> this by using dynamic SQL or by writing a CLR procedure that calculates
> the column names, but this is what I was hoping to avoid. If you know
> a clever way around this, I'd love to hear it.
A lot of people is screaming for a dynamic PIVOT, but my prediction is
that you will never be able to do this in a SELECT statement.
The reason for this is that a SELECT statement returns table, and a table
should have a fixed set of columns. This is presumption to make it possible
to reuse a SELECT statement in a view or a derived table.
The way around this would be to introduce a new statement that in difference
to SELECT is not composable, but else would have most of the features of
SELECT.
Myself, I only very rarely has the need to run pivot queries.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|