K.A.Bouton wrote:
> I need the output of my sql to be a multidimensional array as follows.
then Chris wrote:
> You won't be able to get an sql query to return in that format (I know
> what you're trying to do, I've used the same chart software).
This is my MSSQL Server solution, and yep, it outputs EXACTLY like
requested. I'm not sufficiently fluent with MySQL, so this may have
some "dialect" translation that needs done.
Also, this IS NOT a scalable solution at all. It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query. Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).
-- Mitch
----------- SQL BEGINS HERE ---------
-- Table of products
CREATE TABLE #Products (
Product CHAR(3) NOT NULL
)
INSERT #Products(Product) VALUES('AAA')
INSERT #Products(Product) VALUES('BBB')
INSERT #Products(Product) VALUES('CCC')
-- Sales history table.
CREATE TABLE #Sales (
Product CHAR(3) NOT NULL ,
Quantity INT NOT NULL ,
SaleYear INT NOT NULL
)
-- Product AAA, Intentionally left out 2003
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 8, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 6, 2004)
-- Product BBB, Intentionally left out 2002
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 5, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 8, 2004)
-- Product CCC, Intentionally left out 2001
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 3, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 5, 2004)
-- Now for the totally UNscalable query.
SELECT p.Product,
COALESCE(y1.TotalQuan, 0) AS Quan2001,
COALESCE(y2.TotalQuan, 0) AS Quan2002,
COALESCE(y3.TotalQuan, 0) AS Quan2003,
COALESCE(y4.TotalQuan, 0) AS Quan2004
FROM #Products AS p
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2001
GROUP BY Product
) AS y1 ON y1.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2002
GROUP BY Product
) AS y2 ON y2.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2003
GROUP BY Product
) AS y3 ON y3.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2004
GROUP BY Product
) AS y4 ON y4.Product = p.Product
-- Cleanup
DROP TABLE #Sales
DROP TABLE #Products
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
