PIVOT To Return Dynamic Rows Horizontally

Hi,

I have a client with the following table structure:
ItemNumber, Name, Description

containing the following Data (example):






ItemNumber

Name

Value


6473764

SDRAM

4GB


6473764

Xeon

2300 Mhz


6473764

Video

256 MB


6473764

Bus

1300 Mhz


6473759

SDRAM

2GB


6473759

Xeon

2000 Mhz


6473759

Video

128 MB


6473759

Bus

1066 Mhz

I am trying to use PIVOT to convert this into Columns:






Item Number

SDRAM

Xeon

Video

Bus


6473764

4GB

2300 Mhz

256 MB

1300 Mhz


6473759

2 GB

2000 Mhz

128 MB

1066 Mhz

The problem is that I do not know the names of the columns before I run the query. I retrieve them based on a a list of Item Numbers:
SELECT DISTINCT Name FROM TableName WHERE ItemNumber IN (...)

What I was trying to do is the following, but I am not exactly sure what is wrong:

WITH ProductLines (ItemNumber, ItemName, ItemValue)
AS
(
SELECT ItemNumber, Name, Value FROM ProductItems WHERE ItemNumber IN (...)
)

SELECT * FROM ProductLines -- Don't know the column names
PIVOT
(

... -- Not sure what should come here
FOR ItemNumber IN (...)
)

Thanks a lot,

Aric Levin