Unanswered: PIVOT operator for variable number of transformations
Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:
SELECT IDMerce,  AS [Department-1],  AS [Department-2],  AS
[Department-3],  AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN (, , , )) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
If it's not practically useful, then it's practically useless.