CROSSTAB ::works in access but not in MS SQL 2000

I am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::

Table 1 --> Info
InfoID
Name

Table 2 --> Item
InfoID
Num
Value

TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num

in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all

does someone knows how to translate it for MS SQL (the table structures are exactly the same)?

thank youI have tried

SELECT Info.Name
,(CASE WHEN Item.Num = 10 THEN Item.value END) AS A
,(CASE WHEN Item.Num = 12 THEN Item.value END) AS B
,(CASE WHEN Item.Num = 15 THEN Item.value END) AS C
,(CASE WHEN Item.Num = 100 THEN Item.value END) AS D
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID

i must get one line and i get > 1000 lines !

with GROUP BY Info.Name it doesn't work

what is wrong ?

thank you|||Info

InfoID | Name
............
1 | jean
2| Pierre

------------

Item

InfoID | Num | Value
...............
1 | 400 | 1_400
1 | 10 | 1_10
1 | 15 | 1_15
1 | 12 | 1_12
1 | 100 | 1_100
1 | 40 | 1_40
2 | 400 | 2_400
2 | 10 | 2_10
2 | 15 | 2_15
2 | 12 | 2_12
2 | 100 | 2_100
2 | 40 | 2_40

I must get ::

Name | A | B | C | D
................
Jean | 1_10 | 1_12 | 1_15 | 1_100
Pierre | 2_10 | 2_12 | 2_15 | 2_100|||Dunno if you have gotten a response yet. Crosstab is a native Access function that is not available in SQL 2000. You can google ags crosstab or RAC or SQL for a 3rd party add-on to SQL.

Regards,

hmscott