CONCAT or CASE?

1

I want to CONCAT all ProductCategories returned from a Query to allow analyse sales of a product based on the ProductCategories . We have 100's of ProductCatagories but some Products have 5 ProductCatagories applied others say 10 . Is there any easy way to CONCAT all From this Column or CASE all into row or Cell ?

Hope that makes sense

SELECT      
  
max ( SalesInvoices.EffectiveDate) AS EffDate, 
GROUP_CONCAT(ProductCategories.ProductCategoryId  SEPARATOR ', ') FROM <ProductCategories.ProductCategoryId,
Products.ProductId, 
Products.ProductDescription,
SalesInvoiceItems.Quantity,
  Products.DC_001_TXT AS Story,
(SELECT documentdata FROM Documents WHERE tablename LIKE 'Products' AND documentTitle LIKE 'Thumb' AND DocumentArchived = 0 AND Identifier = Products.Product) AS Thumbnail


FROM            

Products INNER JOIN
                         SalesInvoiceItems ON Products.Product = SalesInvoiceItems.Product INNER JOIN
                         SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice INNER JOIN
                         ProductProductCategories ON Products.Product = ProductProductCategories.Product INNER JOIN
                         ProductCategories ON ProductProductCategories.ProductCategory = ProductCategories.ProductCategory


GROUP BY SalesInvoices.EffectiveDate, Products.ProductId, SalesInvoiceItems.Quantity, Products.DC_001_TXT,Products.Product,
Products.ProductDescription


ORDER BY Products.ProductId

image
image.png1142×258 9.84 KB

2

GROUP_CONCAT is not a SQL Server function. Are you using MySQL?

3

That might explain why it wouldn't work , I'm on MS SQL 2012 . What other options would I have

4

The classic answer is FOR XML PATH('')

Good discussion here: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server

5

Thanks , just stumbled on that . Will get reading

6

Ok so , I'm getting there with the XML , The below query is returning CommisionId and ClassificationID , What I cannot work out is how to tie it up to the Product ID as currently it is returning all all results

SELECT TOP 100 
    ClassificationId = STUFF (( SELECT ',' + ClassificationId FROM Classifications  FOR XML PATH ('')), 1, 1, ''  )
  , Sum(SalesOrderItems.Quantity)
   ,SUM(SalesOrderItems.ItemValue)
   ,Products.ProductId
   ,Products.ProductDescription
   ,
   
   CommissionClass = STUFF ((  SELECT ',' + CommissionClassId FROM CommissionClasses FOR XML PATH ('')), 1, 1, ''  )
  
    
   ,MAX(SalesOrders.EffectiveDate) AS expr1

  ,SUM(SalesOrderItems.ItemValue/SalesOrderItems.Quantity) AS UnitPrice
  , SUM(Products.TotalStandardCost)
   ---SUM ((SalesOrderItems.ItemValue/SalesOrderItems.Quantity)/Products.TotalStandardCost) as Margin
  ---- SUM (Products.TotalStandardCost*SalesOrderItems.Quantity) AS TotalUnitCost
---,SUM(SalesOrderItems.ItemValue-TotalStandardCost*SalesOrderItems.Quantity) AS GrossProfit
 
 
  FROM dbo.SalesOrderItems
  INNER JOIN dbo.SalesOrders
    ON SalesOrderItems.SalesOrder = SalesOrders.SalesOrder
  INNER JOIN dbo.Products
    ON SalesOrderItems.Product = Products.Product
  INNER JOIN dbo.Classifications
    ON Classifications.Classification = Classifications.Classification
  INNER JOIN dbo.Customers
    ON SalesOrders.Customer = Customers.Customer
  INNER JOIN dbo.CommissionClasses
    ON Customers.CommissionClass = CommissionClasses.CommissionClass
 
 
 where (CommissionClasses.CommissionClassId IN ('JOG', 'LIN','SCO','EIR','LIM','WES','EAS','ESZ','SOZ','HOZ' ))
AND (Classifications.ClassificationId IN ('CLOCK'))
 

  GROUP BY 
          
         
		 
 
        
          Products.ProductId,
		  products.ProductDescription