SELECT
/* <non-pivoted column>,[first pivoted column]
AS<column name>,[second pivoted column] AS <column name>
,
...[last pivoted column]
ItemCode, ItemName,AS<column name> */
[1] AS Price1,
[2] AS Price2,
[3] AS Price3,
[4] AS Price4,
[5] AS Price5,
[6] AS Price6,
[7] AS Price7,
[8] AS Price8,
[9] AS Price9,
[10] AS Price10
FROM
-- <this is the query that produces the data>
(SELECT A.ItemCode, A.ItemName, B.PriceList, B.Price
FROM OITM A INNER JOIN ITM1 B ON A.ItemCode=B.ItemCode
WHERE A.ItemCode LIKE '11%') AS SourceTable
PIVOT
(
-- <aggregation function><column that contains the values that will become column headers>
MAX(Price)
FOR
--PriceList IN
--([first pivoted column], [second pivoted column],... [last pivoted column])
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) AS pvt
ORDER BY ItemCode;
here is the equivalent code for SQL SERVER 2000
SELECT A.ItemCode, A.ItemName,
MAX(CASE B.PriceList WHEN 1 THEN B.Price ELSE 0 END) AS Price1,
MAX(CASE B.PriceList WHEN 2 THEN B.Price ELSE 0 END) AS Price2,
MAX(CASE B.PriceList WHEN 3 THEN B.Price ELSE 0 END) AS Price3,
MAX(CASE B.PriceList WHEN 4 THEN B.Price ELSE 0 END) AS Price4,
MAX(CASE B.PriceList WHEN 5 THEN B.Price ELSE 0 END) AS Price5,
MAX(CASE B.PriceList WHEN 6 THEN B.Price ELSE 0 END) AS Price6,
MAX(CASE B.PriceList WHEN 7 THEN B.Price ELSE 0 END) AS Price7,
MAX(CASE B.PriceList WHEN 8 THEN B.Price ELSE 0 END) AS Price8,
MAX(CASE B.PriceList WHEN 9 THEN B.Price ELSE 0 END) AS Price9,
MAX(CASE B.PriceList WHEN 10 THEN B.Price ELSE 0 END) AS Price10
FROM OITM A INNER JOIN ITM1 B ON A.ItemCode=B.ItemCode
GROUP BY A.ItemCode, A.ItemName
ORDER BY ItemCode
GO
Comments