SQL Server - Combining rows in View

Question

I am creating a view that is supposed to show the full details for a pizza per OrderID. Although when I run my view its printing out two rows for OrderID because it has more than one topping. How can I combine those rows in my view so both toppings will show under the "ToppingsDescription" column and the "Total Price" column will be updated (Large pizza = $15 and $0.30 per topping so the updated price should be 15.60 for the large and two toppings). Any suggestions on what to do?

CREATE VIEW PizzaPerOrder_vw
AS
SELECT PO.PizzaOrderID, PizzaSizeDesc, PizzaSpecialInstructions, ToppingsDescription, SUM(T.ToppingsPrice + PizzaSizePrice) AS 'Total Price'
FROM dbo.PizzaOrder AS PO
INNER JOIN dbo.Pizza AS P ON PO.PizzaOrderID = P.PizzaOrderID
INNER JOIN dbo.PizzaSpecialInstructions AS PI ON P.PizzaID = PI.PizzaID
INNER JOIN dbo.PizzaToppings AS PT ON P.PizzaID = PT.PizzaID
INNER JOIN dbo.Toppings AS T ON PT.ToppingsID = T.ToppingsID
INNER JOIN dbo.PizzaSize AS PS ON P.PizzaSizeID = PS.PizzaSizeID
GROUP BY PO.PizzaOrderID, PS.PizzaSizeDesc, PI.PizzaSpecialInstructions, T.ToppingsDescription;

Do you want a single row per pizza? Or the Total Price repeated for each row? — dnoeth, Nov 20 '18 at 16:48
But still all toppings, as list? What's your SQL Server version? — dnoeth, Nov 20 '18 at 16:52
@dnoeth yes exactly and the price updated accordingly. 2017 version — Cameron Cole, Nov 20 '18 at 16:53

score 1 · Accepted Answer · answered Nov 20 '18 at 16:56

In SQL Server 2017 there's a new aggregate function named STRING_AGG:

CREATE VIEW PizzaPerOrder_vw
AS
SELECT PO.PizzaOrderID, PizzaSizeDesc, PizzaSpecialInstructions, 
   STRING_AGG(ToppingsDescription, ',') AS Toppings,
   SUM(T.ToppingsPrice) + MAX(PizzaSizePrice) AS 'Total Price' 
FROM dbo.PizzaOrder AS PO
INNER JOIN dbo.Pizza AS P ON PO.PizzaOrderID = P.PizzaOrderID
INNER JOIN dbo.PizzaSpecialInstructions AS PI ON P.PizzaID = PI.PizzaID
INNER JOIN dbo.PizzaToppings AS PT ON P.PizzaID = PT.PizzaID
INNER JOIN dbo.Toppings AS T ON PT.ToppingsID = T.ToppingsID
INNER JOIN dbo.PizzaSize AS PS ON P.PizzaSizeID = PS.PizzaSizeID
GROUP BY PO.PizzaOrderID, PS.PizzaSizeDesc, PI.PizzaSpecialInstructions;

This returns the toppings in random order, if you want a specific order:

STRING_AGG(ToppingsDescription, ',') WITHIN GROUP (ORDER BY ToppingsDescription) AS Toppings

It might be more efficient to aggregate the PizzaToppings before the join using a CTE.

SQL Server - Combining rows in View

1 Answers1