Skip to content
Advertisement

Covert Access Cross tab to sql

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement