Group by with join (many to many)

Question

I have 2 tables, Product and Component

CREATE TABLE Product(
ProductId uniqueidentifier DEFAULT NEWID(),
ProductName nvarchar(25) NOT NULL,
CONSTRAINT PK_Product PRIMARY KEY (ProductId))


CREATE TABLE Component
(
    ComponentId uniqueidentifier DEFAULT NEWID(),
    ComponentName nvarchar(25) NOT NULL,
    CONSTRAINT PK_Component PRIMARY KEY (ComponentId)
)

I need a connection "many to many", so I created third table

CREATE TABLE Product_Component(
idProduct_Component uniqueidentifier DEFAULT NEWID(),
idProduct uniqueidentifier, 
idComponent uniqueidentifier,

 CONSTRAINT PK_idProduct_Component PRIMARY KEY (idProduct_Component),

CONSTRAINT FK_idComponent FOREIGN KEY (idComponent)
REFERENCES Component (ComponentId),
CONSTRAINT FK_idProduct FOREIGN KEY (idProduct)
REFERENCES Product (ProductId))

I added some data, and now I can select it from tables. Products can have many components, and components are in many products. Now I have in Product 2 rows - Cake and Bread. In Component I have 3 rows - Sugar, Salt and Flour. I added values in table Product_Component and now I have somethink like Cake includes Sugar and Flour, Bread includes Salt and Flour. I use query like this

SELECT Product.ProductName, Component.ComponentName FROM Product_Component
JOIN Component 
ON Product_Component.idComponent = Component.ComponentId 
JOIN Product
ON Product_Component.idProduct = Product.ProductId
WHERE Product.ProductName = 'Bread'

and i see all bread's components, but every row is something like

bread | salt
bread | flour

and i want to see something like this

bread | salt
      | flour
      | some other component

I tried

SELECT Product.ProductName, Component.ComponentName FROM Product_Component
JOIN Component 
ON Product_Component.idComponent = Component.ComponentId 
JOIN Product
ON Product_Component.idProduct = Product.ProductId
WHERE Product.ProductName = 'Bread'
GROUP BY Product.ProductName

but I have message

Column 'Component.ComponentName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help to make correct query.

Answer 1

I think it will be better and more easier to do this in your client side application. SQL is not about formatting.

However, if you want to do this any way in SQL, you can use the FOR XML to group concatenation of the components name for each grouped product like this:

SELECT 
  p.ProductName, 
  STUFF((
    SELECT ', ' + c2.ComponentName 
    FROM Component AS c2
    WHERE c2.idComponent = pc.idComponent 
    FOR XML PATH (''))
  ,1,2,'') AS ComponentsNames
FROM Product_Component AS pc
JOIN Component         AS c ON pc.idComponent = c.ComponentId 
JOIN Product           AS p ON pc.idProduct   = p.ProductId
WHERE p.ProductName = 'Bread'
GROUP BY p.ProductName;