SQL: Field Consolidation

Question

While using this query, I am have a result that I want, but I need to consolidate the Beer.Name instances. Such that, with my output I have multiple entries of Beer.Name and their quantities, when I would like to consolidate both the CustomerID and Beer.Name into single entities. How would I do this?

SELECT C.CustomerID, Beer.Name,sum (quantitysupplied)as 'Total Quantity'
FROM Customer C
INNER JOIN CustomerOrder
    ON C.CustomerID = CustomerOrder.CustomerID 
INNER JOIN CustomerOrderLine
    ON CustomerOrderLine.CustomerOrderID = CustomerOrder.CustomerOrderID 
INNER JOIN Product
    ON CustomerOrderLine.ProductID =Product.ProductID 
INNER JOIN Beer
    ON Product.BeerID = Beer.BeerID
GROUP BY C.CustomerID, Beer.Name, CustomerType
HAVING CustomerType LIKE 'C'

Thank you.

This is the output:

      CustomerID  Name                           Total Quantity
 ----------- ------------------------------ --------------
  1001        Ale Heads                      11
 1002        Ale Heads                      8
  1003        Ale Heads                      13
  1004        Ale Heads                      3
  1006        Ale Heads                      4
  1007        Ale Heads                      14
  1008        Ale Heads                      4
 1009        Ale Heads                      4

It is quite long, but it this is pretty representative, I want to have only one instance of the beer name and Customer ID consolidated into something else.

Could you write out your current output and what you want it to look like? I don't quite understand. — David Brunow, Dec 02 '12 at 23:25
based on your explanation it sounds like you want this -- http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Taryn, Dec 02 '12 at 23:26

score 0 · Answer 1 · answered Dec 03 '12 at 00:19

0

Is this what you are after?

SELECT CAST(C.CustomerID AS varchar) + ': ' + Beer.Name AS CustomerBeer, Sum(quantitysupplied) AS 'Total Quantity'
FROM etc.

answered Dec 03 '12 at 00:19

Dale M

2,453
1
13
21

SQL: Field Consolidation

1 Answers1