I need this converted to a SQL view. This is the code from MS Access:
TRANSFORM Count(Products.Id) AS AntalOfId SELECT Products.[id] FROM Sold INNER JOIN (Products INNER JOIN Cat ON Products.BelsCategoryId = Cat.[Id]) ON Sold.BelsProductId = Products.[id] GROUP BY Products.[id] PIVOT Sold.Year;
Any ideas how to re write this into SQL ?
In products I have id, BelsCategoryID. In Categories, I have ID. In Sold, I have BelsProductID and year. – This table shows weather a product is sold in a given year.
I want the result with year as a header and then 0 or 1, if the product has been sold in a given year
Advertisement
Answer
TRANSFORM
doesn’t have an exact match in other databases, because it can produce a variable number of columns. For a given set of years, you can use conditional aggregation:
SELECT SUM(CASE WHEN Sold.Year = 2020 THEN 1 ELSE 0 END) as year_2020, SUM(CASE WHEN Sold.Year = 2019 THEN 1 ELSE 0 END) as year_2019, SUM(CASE WHEN Sold.Year = 2018 THEN 1 ELSE 0 END) as year_2018 FROM Products INNER JOIN Cat ON Products.BelsCategoryId = Cat.[Id] INNER JOIN Sold ON Sold.BelsProductId = Products.[id] GROUP BY Products.[id]
If you want a flexible number of years, you need to use dynamic SQL.