Stuck on trying to pivot on four columns, I have code that works one one column but can't get it to work for more than one... any ideas much apreciated.


Here's my code which works for one column:

DECLARE @query VARCHAR(4000)
DECLARE @prodtype VARCHAR(2000)
SELECT @prodtype = STUFF(( SELECT DISTINCT
'],[' + ltrim(([Product Type]))
FROM ProductCosts
ORDER BY '],[' + ltrim(([Product Type]))
FOR XML PATH('')
), 1, 2, '') + ']'

SET @query =

'SELECT * FROM
(
SELECT [ProductID], [Product Type], [Product Value]
FROM ProductCosts
)t

PIVOT (SUM([Product Value]) FOR [Product Type]
IN ('+@prodtype+')) AS pvt1
'

EXECUTE (@query)

Pivot operations are best handled on the app server (for N-tier applications) or the client (on 2-tier applications).

-PatP