title: t-sql most efficient row to column? crosstab for xml path, pivot
body: <p>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.</p>
  
  <pre><code> 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('')
     ),'&amp;#x20;','') as Products
   from _orders o
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>For example, given the following tables:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>for an order I need to output:</p>
  
  <pre><code>orderid     Products
  ----------- -----------------------
  1             100  158  234
  2             125
  3             101  105  212  250
  </code></pre>
  
  <p>or</p>
  
  <pre><code>orderid     Products
  ----------- -----------------------
  1           100|158|234
  2           125
  3           101|105|212|250
  </code></pre>
  
  <p>Thoughts or suggestions?  I am using SQL Server 2k5.</p>
  
  <p>Example Setup: </p>
  
  <pre><code>   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
  
   CREATE CLUSTERED INDEX IX_CL__orders on _orders(OrderId)
   CREATE NONCLUSTERED INDEX IX_NCL__orders on _orders(OrderId)
   INCLUDE (CustomerId)
  
   CREATE CLUSTERED INDEX IX_CL_details on _details(OrderId)
   CREATE NONCLUSTERED INDEX IX_NCL_details on _details(OrderId)
   INCLUDE (DetailId,ProductId)
  </code></pre>
  
  <p>using FOR XML PATH:</p>
  
  <pre><code> 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('')
     ),'&amp;#x20;','') as Products
   from _orders o
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>orderid     Products
  ----------- -----------------------
  1             100  158  234
  2             125
  3             101  105  212  250
  </code></pre>