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?

Are you wanting to concatenate the product ID's?

Icode4food
– Icode4food

2010-08-05 17:12:23 +00:00
Commented Aug 5, 2010 at 17:12 — Icode4food
– Icode4food, Commented Aug 5, 2010 at 17:12
Yes, I just need to get product id's for each order number

Pinu
– Pinu

2010-08-05 17:13:45 +00:00
Commented Aug 5, 2010 at 17:13 — Pinu
– Pinu, Commented Aug 5, 2010 at 17:13
2000 - test db 2008 - prob db

Pinu
– Pinu

2010-08-05 17:17:05 +00:00
Commented Aug 5, 2010 at 17:17 — Pinu
– Pinu, Commented Aug 5, 2010 at 17:17

OMG Ponies · Accepted Answer · 2010-08-05 17:18:44Z

2

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

answered Aug 5, 2010 at 17:18

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JeffO · Accepted Answer · 2010-08-05 17:18:40Z

0

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

answered Aug 5, 2010 at 17:18

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Collectives™ on Stack Overflow

sql query converting rows to columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related