PIVOT command
-
Can anyone post some code which will allow me not to hardcode column names with the new PIVOT command. For eg (this doesn't work but gives the idea)
- the key bit is :
FOR OrderYear IN (*)
WITH ProductSales(ProductID, OrderYear, OrderTotal)
AS (
SELECT
det.productID,
YEAR(hdr.orderdate),
det.linetotal
FROM
sales.salesorderdetail det
JOIN sales.salesorderheader hdr
ON det.salesorderid = hdr.salesorderid
)
SELECT
*
FROM
ProductSales
PIVOT (
SUM(OrderTotal)
FOR OrderYear IN (*)
) AS ProductSalesPivot
ORDER BY
ProductSalesPivot.ProductID
ty
-
Off the top of my head, this sounds like a dynamic SQL sort of thing..
Take care..- Ward Pond
blogs.technet.com/wardpond -
As Ward indicates, tt requires dynamic SQL. You can't use a query to define the column names, unfortunately.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply