SQL查询：将行转换为列

Question

我有两个表格

订单表 - 订单ID- 主键
订单详情表 - 订单ID是从订单表中引用的外键。

订单详情表包含了特定订单中所有订购产品的信息。

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

我们如何在一次查询中获得这个结果？

- Pinu

---

你想要连接产品ID吗？ - Icode4food

是的，我只需要获取每个订单号对应的产品ID。 - Pinu

2000 - 测试数据库 2008 - 问题数据库 - Pinu

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

- OMG Ponies

Answer 2

Select 
     odt.OrderID
   , (Select Cast(ProductID as Varchar(20) + ', ' 
      from [Order Detail Table] as odt1
      where odt.OrderID = odt1.OrderID
      FOR XML PATH('')
      )                                     as ProductID
   , Sum(odt.UnitPrice * odt.Quantity)      as [Total Items]
From [Order Detail Table] as odt
Group By OrderID

- JeffO