Could anybody tell me how to convert vertical data into horizontal data?

Could anybody tell me how to convert vertical data into horizontal data?
I have a one-to-many relationship in sql server 2K
Product, ProductAccessory, one Product has many ProductAccessories.
My Table design is like this:
Table Product
{
ProdId int,
ProdNameId int,
...
}
Table ProductAccessory
{
ProdId int,
AccNameId int,
AccUnitId int,
...
}
Because one Production has at most 4 ProductAccessory
I want to use a SELECT statement OR function to return
ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ...
Any help will be appreciated!
Thanks a lot
Joseph
YOU CAN USE cursor or Temp table whatever you like.|||What you are trying to do is called a pivot table or a cross-tab query. Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).
|||Got the answer
SELECT ProdId,
SUM(CASE AccSeqnoWHEN 1 THEN Amount ELSE 0 END) AS AccUnitId1,
SUM(CASE AccSeqnoWHEN 2 THEN Amount ELSE 0 END) AS AccUnitId2,
SUM(CASE AccSeqnoWHEN 3 THEN Amount ELSE 0 END) AS AccUnitId3,
SUM(CASE AccSeqnoWHEN 4 THEN Amount ELSE 0 END) AS AccUnitId4
FROM ProductAccessory
GROUP BY ProdId
Just add a fieled AccSeqno
Joseph