Pivot Table in SQL SERVER 2005

SELECT
/* <non-pivoted column>,

[first pivoted column] AS <column name> ,

[second pivoted column] AS <column name> ,

...

[last pivoted column] AS <column name> */

ItemCode, ItemName,
[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>
MAX(Price)

FOR
-- <column that contains the values that will become column headers>
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