sql query converting rows to columns

Question

I have 2 tables

Order Table - OrderId- Primary key
Order Details Table - OrderID foreign key from order table.

Orderdetail table has the information for all the products which were ordered in the specific order

For example

Order                                             Order Detail Table
ID  CustomerID  OrderDate            ID  OrderID      ProductID      UnitPrice Quantity
1      1       2009-10-1              1    1             5                  5        10
2      2       2009-10-2              2    1             4                  10       100             
3      3       2009-10-3              3    1             7                   8       50                
                                      4    1             2                   5       20
                                      5    2             1                   8       100
                                      6    2             5                   5        1                                              
                                      7    2              4                  10      100
                                      8    3              1                  5       200
                                      9    3              3                 20       100
                                      10   3              2                  5       200



I need to get result like this    
OrderId    ProductID                           Total Items           
1            5,4,7,2                             180 (sum of quantity)
2            1,5,4                               300
3            1,3,2                               500

How do we get this result in one query?

Answer 1

For SQL Server 2005+, use:

SELECT x.orderid,
       STUFF(ISNULL((SELECT ', ' + y.productid           
                       FROM ORDER_DETAILS y
                      WHERE y.orderid = x.orderid
                   GROUP BY y.productid
                    FOR XML PATH ('')), ''), 1, 2, ''),
       x.total_items
  FROM (SELECT od.orderid,
               SUM(od.quantity) AS total_items
          FROM ORDER_DETAILS od
      GROUP BY od.orderid) x