arup_kc (6/23/2009)
Few days before i have used this pivot function and it worked efficiently.
It works, but it is quite useless if you want to pivot for a dynamic column list. I had to code for dynamic sql and sp_executesql to achieve it.
Just a few words about performance: internally it does an aggregation on a CASE expression...
[Expr1006] = Scalar Operator(MIN(CASE WHEN [InvoiceNumber]=(1) THEN [BillName] ELSE NULL END)); [Expr1007] = Scalar Operator(MIN(CASE WHEN [InvoiceNumber]=(2) THEN [BillName] ELSE NULL END)); [Expr1008] = Scalar Operator(MIN(CASE WHEN [InvoiceNumber]=(3) THEN [BillName] ELSE NULL END)); [Expr1009] = Scalar Operator(MIN(CASE WHEN [InvoiceNumber]=(4) THEN [BillName] ELSE NULL END))
(taken from the actual execution plan)
Regards,
Gianluca
-- Gianluca Sartori