CODEWITHSUNDEEP
sqlsql-server
Kim
Kim

Reputation: 191

SQL to Merge Multiple Child Records into a single row with new columns

I have a table called OrderDetail. I also have a child table called PriceCategories.

Each OrderDetail can have multiple PriceCategories (eg A = $1, B = 2$, C = 3$, etc...)

I want to somehow allow a single resulting record when I am doing a SQL Query on OrderDetail joined to PriceCategories, but returning each record in PriceCategories as columns in a single row.

For example using a normal Join I would end up with.

OrderID | PriceCat | Amount 
1            A          1
1            B          2
1            C          3

What I want to end up with is something like;

OrderId | CatAAmount | CatBAmount | CatCAmount
1           1             2           3

Is there anyway within a SQL statement that can achieve this?

Upvotes: 0

Views: 694

Answers (2)

Chiranjeevi Katta
Chiranjeevi Katta

Reputation: 31

Using pivot.

SELECT OrderID, [A] CatAAmount,[B] CatBAmount, [C] CatCAmount  
FROM   
(SELECT OrderID, PriceCat, Amount    
FROM test) p  
PIVOT  
(  
max (Amount)  
FOR PriceCat IN  
( [A],[B],[C])  
) AS pvt;

If you have more categories you can simply add them in the 'FOR' and 'SELECT' clause. Please refer http://sqlfiddle.com/#!18/e19ac/13/0

Upvotes: 1

Caleb
Caleb

Reputation: 359

Use a case statement -

SELECT order_id,
   CASE
     WHEN pricecat = 'A' THEN
      amount
   END AS cataamount,
   CASE
     WHEN pricecat = 'B' THEN
      amount
   END AS catbamount,
   CASE
     WHEN pricecat = 'C' THEN
      amount
   END AS catcamount
FROM table

Upvotes: 1

Related Questions