I am looking for the most performant way to turn rows into columns. I have a requirement to output the contents of the db (not actual schema below, but concept is similar) in both fixed width and delimited formats. The below FOR XML PATH query gives me the result I want, but when dealing with anything other than small amounts of data, can take awhile.

 select orderid
   ,REPLACE((  SELECT '  ' + CAST(ProductId as varchar)
       FROM _details d
       WHERE d.OrderId = o.OrderId
       ORDER BY d.OrderId,d.DetailId
       FOR XML PATH('')
   ),' ','') as Products
 from _orders o

I've looked at pivot but most of the examples I have found are aggregating information. I just want to combine the child rows and tack them onto the parent.

I should also point out I don't need to deal with the column names either since the output of the child rows will either be a fixed width string or a delimited string.

For example, given the following tables:

OrderId     CustomerId
----------- -----------
1           1
2           2
3           3

DetailId    OrderId     ProductId
----------- ----------- -----------
1           1           100
2           1           158
3           1           234
4           2           125
5           3           101
6           3           105
7           3           212
8           3           250

for an order I need to output:

orderid     Products
----------- -----------------------
1             100  158  234
2             125
3             101  105  212  250

or

orderid     Products
----------- -----------------------
1           100|158|234
2           125
3           101|105|212|250

Thoughts or suggestions? I am using SQL Server 2k5.

Example Setup:

   create table _orders (
  OrderId int identity(1,1) primary key nonclustered
  ,CustomerId int
 )

 create table _details (
  DetailId int identity(1,1) primary key nonclustered
  ,OrderId int 
  ,ProductId int
 )

 insert into _orders (CustomerId)
 select 1
 union select 2
 union select 3

 insert into _details (OrderId,ProductId)
 select 1,100
 union select 1,158
 union select 1,234
 union select 2,125
 union select 3,105
 union select 3,101
 union select 3,212
 union select 3,250

using FOR XML PATH:

 select orderid
   ,REPLACE((  SELECT '  ' + CAST(ProductId as varchar)
       FROM _details d
       WHERE d.OrderId = o.OrderId
       ORDER BY d.OrderId,d.DetailId
       FOR XML PATH('')
   ),' ','') as Products
 from _orders o

which outputs what I want, however is very slow for large amounts of data. One of the child tables is over 2 million rows, pushing the processing time out to ~ 4 hours.

orderid     Products
----------- -----------------------
1             100  158  234
2             125
3             101  105  212  250