I'm fairly new to SQL and I'm trying to get my head around the following problem - I'm attempting to get a list of products broken down so we can see how many of each one has been sold every year
I have two tables; Orders (which contains JobNum and Date) and Products (with JobNum, Fig, Fype, Size and Quantity fields). Due to the products being custom made a product is an amalgamation of the fig, type and size fields. For example: To get a listing of all the product variations created I can do:
SELECT DISTINCT Fig,
Type,
Size
FROM Product;
And to get a list of quantities for all products made in a calendar year this query seems to work: (Date is the format 'dd-mm-yyyy' hence the LIKE '%2002' to filter by year)
SELECT product.fig,
product.type,
product.size,
Sum(product.qty) AS Quantity
FROM orders.dbf
INNER JOIN product.dbf
ON orders.jobnum = product.jobnum
WHERE orders.date LIKE '%2002'
GROUP BY product.fig,
product.type,
product.size
Which gives the data for 1 year outputted like this:
Fig Type Size Qty
AA B 2 1
My question is how can I pull out the data to get an output like this?
Fig Type Size 2001 2002 2003...
AA B 2 1 2 4
BB C 4 4 6 7
I can think how produce this in a program but I'd like to see if it's possible to do just in SQL?
Many thanks in advance.
EDIT- Can I just point out that Product.Type can be blank in some cases and in some years it is possible for zero instances of a product to be sold, so for that year the corresponding quantity amount could be blank or 0.
Although I have not specifically used dbase since about 1986, I am guessing they have implemented a common IIF() functionality as other languages. I am more used to FoxPro/Visual Foxpro historically, but here is the basis of what you need, and if you are using Visual Foxpro OleDB, you should be fine.
What is happening here, as you are going through, the group by will be based on each fig, type, size, no problem. Now, to create your PIVOT, I am just doing a sum( IIF()) based on the order date. If the year of the order date in question equals the respective year, add that quantity to that column's total, otherwise, sum a zero value.
SELECT
p.fig,
p.type,
p.size,
SUM( IIF( YEAR( o.date ) = 2002, p.qty, 0 )) as P2002Qty,
SUM( IIF( YEAR( o.date ) = 2003, p.qty, 0 )) as P2003Qty,
SUM( IIF( YEAR( o.date ) = 2004, p.qty, 0 )) as P2004Qty,
SUM( IIF( YEAR( o.date ) = 2005, p.qty, 0 )) as P2005Qty,
SUM( IIF( YEAR( o.date ) = 2006, p.qty, 0 )) as P2006Qty,
SUM( IIF( YEAR( o.date ) = 2007, p.qty, 0 )) as P2007Qty,
SUM( IIF( YEAR( o.date ) = 2008, p.qty, 0 )) as P2008Qty,
SUM( IIF( YEAR( o.date ) = 2009, p.qty, 0 )) as P2009Qty,
SUM( IIF( YEAR( o.date ) = 2010, p.qty, 0 )) as P2010Qty,
SUM( IIF( YEAR( o.date ) = 2011, p.qty, 0 )) as P2011Qty,
SUM( IIF( YEAR( o.date ) = 2012, p.qty, 0 )) as P2012Qty,
SUM( IIF( YEAR( o.date ) = 2013, p.qty, 0 )) as P2013Qty
from
orders o
INNER JOIN product p
ON o.jobnum = p.jobnum
group by
p.fix,
p.type,
p.size
Now, one note... If the results for whatever strange reason gives zeros in most columns, it is because the engine is predetermining the column size width based on the first "test" record to run the query before it actually executes. If so, then two options to adjust... Change each row to one of the following
FROM:
SUM( IIF( YEAR( o.date ) = 2002, p.qty, 0 )) as P2002Qty,
TO: ( so it FORCES allocated space to 7 digit positions )
SUM( IIF( YEAR( o.date ) = 2002, p.qty, 0000000 )) as P2002Qty,
OR: (so it always uses the qty size, but multiplies by 1 or 0 so it still uses basis of qty column)
SUM( p.qty * IIF( YEAR( o.date ) = 2002, 1, 0 )) as P2002Qty,