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
GROUP_CONCAT is not a SQL Server function. Are you using MySQL?
That might explain why it wouldn't work , I'm on MS SQL 2012 . What other options would I have
Thanks , just stumbled on that . Will get reading
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